I have a little problem that I'm struggling with on excel. I do have a number of macros (VB) enbedded which work fine but this one is a little beyond me.

Basically it is to add rainfall from 8 seperate cells (6***7):
=AddRainfall2(V965,AF965,AQ965,BI965,BR965,CD965,C P965,CY965)
The problem is any cell may contain 'TR', a number value (0.2 ~ 24 or anything etc) or may be blank, eg 8 cells may contain the following:
TR, TR, 0.2, ' ', ' ' , 0.1, ' ', 0.1 ~ the result of the 8 I would expect to see 0.4
or
TR, TR, ' ', ' ', ' ' , ' ', ' ', TR ~ the result of the 8 I would expect to see TR
or
2.0, 0.4, 3.0, 0.8, 0.3, 1.0, 0.2, 0.2 ~ the result of the 8 I would expect to see 7.9
As you can see there is 8x8 combinations = 64

If I didn't have 'TR' it would be quite simple to add up.

Any help from anyone that knows how to use excel would be nuch appreciated.

Many thanks

Keith (Southend)

I don't use advanced Excel much but I suspect that something along the lines
of SUMIF(range, "TR") might work. The syntax here might not be exactly
right, but might be a starting point for a more accurate answer.

Keith Harris wrote:

I have a little problem that I'm struggling with on excel. I do have a number
of macros (VB) enbedded which work fine but this one is a little beyond me.

Basically it is to add rainfall from 8 seperate cells (6***7):
=AddRainfall2(V965,AF965,AQ965,BI965,BR965,CD965,C P965,CY965)
The problem is any cell may contain 'TR', a number value (0.2 ~ 24 or
anything etc) or may be blank, eg 8 cells may contain the following: TR, TR,
0.2, ' ', ' ' , 0.1, ' ', 0.1 ~ the result of the 8 I would expect to see 0.4
or TR, TR, ' ', ' ', ' ' , ' ', ' ', TR ~ the result of the 8 I would expect
to see TR or
2.0, 0.4, 3.0, 0.8, 0.3, 1.0, 0.2, 0.2 ~ the result of the 8 I would expect
to see 7.9 As you can see there is 8x8 combinations = 64

If I didn't have 'TR' it would be quite simple to add up.

Any help from anyone that knows how to use excel would be nuch appreciated.

Many thanks

Keith (Southend)

Not sure how you have this set up but if you could force it to substitute 0.0
for TR then that would surely solve your problem. Perhaps it might be necessary
to use something like 0.001 rather than 0.0 so that you would have a total
above 0.0 if there were any traces.

--
Norman Lynagh
Tideswell, Derbyshire
303m a.s.l.
http://peakdistrictweather.org
Twitter: @TideswellWeathr

On Sunday, 18 June 2017 21:11:30 UTC+1, Norman Lynagh wrote:
Keith Harris wrote:

I have a little problem that I'm struggling with on excel. I do have a number
of macros (VB) enbedded which work fine but this one is a little beyond me.

Basically it is to add rainfall from 8 seperate cells (6***7):
=AddRainfall2(V965,AF965,AQ965,BI965,BR965,CD965,C P965,CY965)
The problem is any cell may contain 'TR', a number value (0.2 ~ 24 or
anything etc) or may be blank, eg 8 cells may contain the following: TR, TR,
0.2, ' ', ' ' , 0.1, ' ', 0.1 ~ the result of the 8 I would expect to see 0.4
or TR, TR, ' ', ' ', ' ' , ' ', ' ', TR ~ the result of the 8 I would expect
to see TR or
2.0, 0.4, 3.0, 0.8, 0.3, 1.0, 0.2, 0.2 ~ the result of the 8 I would expect
to see 7.9 As you can see there is 8x8 combinations = 64

If I didn't have 'TR' it would be quite simple to add up.

Any help from anyone that knows how to use excel would be nuch appreciated.

Many thanks

Keith (Southend)

Not sure how you have this set up but if you could force it to substitute 0.0
for TR then that would surely solve your problem. Perhaps it might be necessary
to use something like 0.001 rather than 0.0 so that you would have a total
above 0.0 if there were any traces.

--
Norman Lynagh
Tideswell, Derbyshire
303m a.s.l.
http://peakdistrictweather.org
Twitter: @TideswellWeathr

Thanks John, I just had a go with that function, but atm, no joy.
I was also thinking about changing the TR to 0.01 but that would have to be a change to the synop code eg: 69907 to ?

Keith (Southend)

On 18/06/2017 20:25, Keith Harris wrote:
I have a little problem that I'm struggling with on excel. I do have a number of macros (VB) enbedded which work fine but this one is a little beyond me.

Basically it is to add rainfall from 8 seperate cells (6***7):
=AddRainfall2(V965,AF965,AQ965,BI965,BR965,CD965,C P965,CY965)
The problem is any cell may contain 'TR', a number value (0.2 ~ 24 or anything etc) or may be blank, eg 8 cells may contain the following:
TR, TR, 0.2, ' ', ' ' , 0.1, ' ', 0.1 ~ the result of the 8 I would expect to see 0.4
or
TR, TR, ' ', ' ', ' ' , ' ', ' ', TR ~ the result of the 8 I would expect to see TR
or
2.0, 0.4, 3.0, 0.8, 0.3, 1.0, 0.2, 0.2 ~ the result of the 8 I would expect to see 7.9
As you can see there is 8x8 combinations = 64

If I didn't have 'TR' it would be quite simple to add up.

Any help from anyone that knows how to use excel would be nuch appreciated.

Many thanks

Keith (Southend)

My Excel skills wouldn't be up to this, but if you go to
http://windowssecrets.com/forums/ and register they have an Excel forum
(http://windowssecrets.com/forums/for...5-Spreadsheets)
which I'm sure will give you an answer. I am registered there and have
received help to my questions and have been able to help others on occasion.

Well, have a look at:

https://exceljet.net/formula/sum-if-...e-not-equal-to

Maybe my previous example should have been: SUMIF(range, "TR") - I think
this approach should work but, as ever, the syntax needs to be exactly
right.

On Sunday, 18 June 2017 21:36:28 UTC+1, JohnD wrote:
Well, have a look at:

https://exceljet.net/formula/sum-if-...e-not-equal-to

Maybe my previous example should have been: SUMIF(range, "TR") - I think
this approach should work but, as ever, the syntax needs to be exactly
right.

Excellent and very helpful forum, really quick replies, yes, there are a couple of ways of doing it. John, you were very close to the easiest solution which is:

=IF(SUM(V965,AF965,AQ965,BI965,BR965,CD965,CP965,C Y965)0,SUM(V965,AF965,AQ965,BI965,BR965,CD965,CP9 65,CY965),"TR")

Happy days.

Many thanks once again for all your replies

Keith (Southend)