Hi
I hope someone can answer this question after a good debate with a friend of mine.

I was calculating some Mean Temps- max and min etc over the last year. My excel spreadsheet carefully rounded the data up to one decimal point. But we discovered that the data didnt match and we hadnt been using the "means rule" where you have to round up or round down depending on the value. My friend calculated the final sum manually, but of course being lazy I want my spreadsheet to do it for me.

E.G of means rule ?
15.05 change 15.1
15.15 leave at 15.1
15.25 change to 15.3
15.35 leave at 15.3
15.45 change to 15.5
15.55 leave at 15.5
15.65 change to 15.7
15.75 leave at 15.7
15.85 change to 15.9
15.95 leave at 15.9
16.05 change to 16.1

Does anyone know how to "programme" a spreadsheet to round up the correct "weather" way to avoid manual calculation.

hope all this makes sense, I did look in the Mr Burts weather observers handbook but I cant find anything relating to this in there.

regards
Paul C
Brampton, Cumbria
www.bramptonweather.co.uk

I thought that you always round up if 5 or greater, i.e., 15.15 will
always become 15.2, and so on.

I have not heard of any other way of doing this (until now).

--
Nick G
Otter Valley, Devon
20 m amsl
http://www.ottervalley.co.uk

Nick Gardner wrote:

I thought that you always round up if 5 or greater, i.e., 15.15 will always
become 15.2, and so on.

I have not heard of any other way of doing this (until now).

The standard meteorological method is to "throw it to the odd". Therefore,
15.15 becomes 15.1 whereas 15.25 becomes 15.3. I believe the logic in this is
to eliminate systematic bias that would be caused if rounding was always up (or
down).

--
Norman Lynagh
Tideswell, Derbyshire
303m a.s.l.

On 29/12/2012 13:01, Norman wrote:
Nick Gardner wrote:

I thought that you always round up if 5 or greater, i.e., 15.15 will always
become 15.2, and so on.

I have not heard of any other way of doing this (until now).

The standard meteorological method is to "throw it to the odd". Therefore,
15.15 becomes 15.1 whereas 15.25 becomes 15.3. I believe the logic in this is
to eliminate systematic bias that would be caused if rounding was always up (or
down).

.... indeed: I've not found a way to do this in Excel and I have to keep
manually checking so as to over-write the automatic calculation it
offers. As noted above, in meteorological statistical use, as far as I
know, we've always 'thrown to the odd'.

The only deviation to this was with actual pressure observations (not
climatological returns) where 0.5mbar was always thrown *down*: this
being 'safer' when setting altimeters etc.

Martin.


--
West Moors / East Dorset
Lat: 50deg 49.25'N, Long: 01deg 53.05'W
Height (amsl): 17 m (56 feet)
COL category: C1 overall

On 29 Dec 2012 13:01:30 GMT
"Norman" wrote:

Nick Gardner wrote:

I thought that you always round up if 5 or greater, i.e., 15.15
will always become 15.2, and so on.

I have not heard of any other way of doing this (until now).

The standard meteorological method is to "throw it to the odd".
Therefore, 15.15 becomes 15.1 whereas 15.25 becomes 15.3. I believe
the logic in this is to eliminate systematic bias that would be
caused if rounding was always up (or down).


According to what I remember of my HNC Numerical Analysis course, the
standard method is to "throw to the even;" I assume the meteorological
reason for being the odd one out is to throw away from zero.

--
Graham P Davis, Bracknell, Berks.
Time flies like an arrow but fruit flies like a banana. [Marx]

On Saturday, December 29, 2012 2:24:24 PM UTC, Graham P Davis wrote:
On 29 Dec 2012 13:01:30 GMT

"Norman" wrote:



Nick Gardner wrote:



I thought that you always round up if 5 or greater, i.e., 15.15

will always become 15.2, and so on.



I have not heard of any other way of doing this (until now).



The standard meteorological method is to "throw it to the odd".

Therefore, 15.15 becomes 15.1 whereas 15.25 becomes 15.3. I believe

the logic in this is to eliminate systematic bias that would be

caused if rounding was always up (or down).





According to what I remember of my HNC Numerical Analysis course, the

standard method is to "throw to the even;" I assume the meteorological

reason for being the odd one out is to throw away from zero.



--

Graham P Davis, Bracknell, Berks.

Time flies like an arrow but fruit flies like a banana. [Marx]

Observers Handbook P 111 Para 8.1.3

"When the instructions specify that the reading is to be reported or recorded only to the nearest whole degree the procedure is as follows;
If the number of tenths in the corrected value is five, "throw to the odd" i.e. round off to the nearest odd whole number; for example 6.5 = 7, 7.5 = 7 8.5 = 9."

In article ,
says...
15.75 leave at 15.7
15.85 change to 15.9
15.95 leave at 15.9
16.05 change to 16.1

Does anyone know how to "programme" a spreadsheet to round up the correct "weather" way to avoid manual calculation.


are you using excel?

=(odd(cell*10))/10 should do it.

--
Alan LeHun
Reply-to is valid. Add "BPSF" to subject: to bypass spam filters.

In article ,
says...
Does anyone know how to "programme" a spreadsheet to round up the correct "weather" way to avoid manual calculation.


are you using excel?

=(odd(cell*10))/10 should do it.


-sigh-
I shall correct my own post.

Odd always rounds UP so it would have to be
=(odd(cell*10-5))/10



--
Alan LeHun
Reply-to is valid. Add "BPSF" to subject: to bypass spam filters.

In article ,
says...
-sigh-
I shall correct my own post.

Odd always rounds UP so it would have to be
=(odd(cell*10-5))/10


-sigh-
For the last time

I have checked this and this appears to do what you want.

=(odd(cell*10-1))/10


10.12 10.1
10.19 10.1
10.22 10.3
10.26 10.3
10.15 10.1
10.25 10.3

--
Alan LeHun
Reply-to is valid. Add "BPSF" to subject: to bypass spam filters.

On Sat, 29 Dec 2012 22:27:22 -0000, Alan LeHun wrote:
10.12 10.1
10.19 10.1
10.22 10.3
10.26 10.3
10.15 10.1
10.25 10.3

That's not quite right. 10.12 - 10.1 is ok, but 10.19 - 10.2, 10.22
- 10.2. The last 3 are ok too. The "different" behaviour occurs at
the mid-point between 10ths i.e. 10.05, 10.15, 10.25, etc. This is
where "throwing to the odd occurs (if you are measuring to the
nearest 10th). 10.05 - 10.1, 10.15 - 10.1, 10.25 - 10.3, 10.35 -
10.3 and so on. In Java you can do this type of rounding - but I
can't remember either what the "name" of this rounding is, nor which
package the rounding functions are in. I will look them both up in
the morning.

--
Freddie
Bayston Hill
Shropshire
102m AMSL
http://www.hosiene.co.uk/weather/
https://twitter.com/#!/BaystonHillWx*for hourly reports*