Home |
Search |
Today's Posts |
![]() |
|
uk.sci.weather (UK Weather) (uk.sci.weather) For the discussion of daily weather events, chiefly affecting the UK and adjacent parts of Europe, both past and predicted. The discussion is open to all, but contributions on a practical scientific level are encouraged. |
Reply |
|
LinkBack | Thread Tools | Display Modes |
#1
![]() |
|||
|
|||
![]()
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) |
#2
![]() |
|||
|
|||
![]()
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. |
#3
![]() |
|||
|
|||
![]()
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 |
#4
![]() |
|||
|
|||
![]()
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) |
#5
![]() |
|||
|
|||
![]()
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. |
#6
![]() |
|||
|
|||
![]()
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. |
#7
![]() |
|||
|
|||
![]()
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Excel template for recording daily local weather. | uk.sci.weather (UK Weather) | |||
(OT) Excel help | uk.sci.weather (UK Weather) | |||
[OT] Excel help | uk.sci.weather (UK Weather) | |||
O/T Excel Help | uk.sci.weather (UK Weather) | |||
Excel help :-) | uk.sci.weather (UK Weather) |