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 Search this Thread Display Modes
  #1   Report Post  
Old June 18th 17, 08:25 PM posted to uk.sci.weather
external usenet poster
 
First recorded activity by Weather-Banter: Jul 2010
Posts: 1,712
Default Excel help?

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   Report Post  
Old June 18th 17, 08:59 PM posted to uk.sci.weather
external usenet poster
 
First recorded activity by Weather-Banter: Mar 2015
Posts: 330
Default Excel help?

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   Report Post  
Old June 18th 17, 09:11 PM posted to uk.sci.weather
external usenet poster
 
First recorded activity by Weather-Banter: Jun 2016
Posts: 4,898
Default Excel help?

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   Report Post  
Old June 18th 17, 09:18 PM posted to uk.sci.weather
external usenet poster
 
First recorded activity by Weather-Banter: Jul 2010
Posts: 1,712
Default Excel help?

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   Report Post  
Old June 18th 17, 09:30 PM posted to uk.sci.weather
external usenet poster
 
First recorded activity by Weather-Banter: Jul 2015
Posts: 149
Default Excel help?

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   Report Post  
Old June 18th 17, 09:36 PM posted to uk.sci.weather
external usenet poster
 
First recorded activity by Weather-Banter: Mar 2015
Posts: 330
Default Excel help?

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   Report Post  
Old June 19th 17, 07:57 AM posted to uk.sci.weather
external usenet poster
 
First recorded activity by Weather-Banter: Jul 2010
Posts: 1,712
Default Excel help?

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel template for recording daily local weather. chipshop uk.sci.weather (UK Weather) 2 September 6th 11 04:29 PM
(OT) Excel help Ken Cook uk.sci.weather (UK Weather) 7 November 29th 10 08:44 PM
[OT] Excel help Keith (Southend) uk.sci.weather (UK Weather) 15 March 18th 08 11:59 PM
O/T Excel Help Keith (Southend) uk.sci.weather (UK Weather) 9 February 20th 05 07:50 AM
Excel help :-) Keith (Southend) uk.sci.weather (UK Weather) 4 February 29th 04 07:21 PM


All times are GMT. The time now is 11:36 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 Weather Banter.
The comments are property of their posters.
 

About Us

"It's about Weather"

 

Copyright © 2017