averaging data

J

James

When averaging data in EXCEL I need to round up at 5 or above only if the
preceding number is odd, if it is even, the number is not rounded up.
Example, 1.5245 rounds to 1.524, 1.5255 rounds up to 1.526. Is it possible to
do this in EXCEL?
 
T

T. Valko

This sounds like a nightmare!

1.5245
1.5255

Are these values calculated? If so, the true value can be more than 4
decimal places. I was able to do it using a rather ugly IF formula based on
every number being set at 4 decimal places or being integers. Do you have
values like:

10.2
0.522
150.009

Biff
 
B

Bob Phillips

See if this works for you

=IF(RIGHT(A1,1)<>"5",ROUND(A1,LEN(MOD(A1,1))-3),
IF(ISEVEN(MID(A1,LEN(A1)-1,1)),ROUNDDOWN(A1,LEN(MOD(A1,1))-3),ROUNDUP(A1,LEN(MOD(A1,1))-3)))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
R

Ragdyer

Try this:

=IF(OR(ISEVEN(LEFT(RIGHT(A1,2))),RIGHT(A1)<"5"),--LEFT(A1,LEN(A1)-1),ROUND(A
1,LEN(A1-INT(A1))-3))
 
D

driller

Hi james, it seems confusing, your subject is titled as averaging data yet
you like to have conditional rounding of each data..where is the question
about averaging data..please explain...
happy holidays.
 
T

T. Valko

You didn't say if these numbers were calculated. If the numbers are
calculated Excel will not display a number like 1.9000 unless it's formatted
to display all the decimal places but the true underlying value of the cell
is 1.9.

Try this:

=IF(MOD(A2,1),IF(LEN(MOD(A2,1))<6,A2,IF(MOD(MID(MOD(A2,1),5,1)+0,2),ROUND(A2,3),TRUNC(A2,3))),A2)

Biff
 
T

T. Valko

0.5 = #VALUE!
1 = #VALUE!
0.25 = 0.2

Biff

Ragdyer said:
Try this:

=IF(OR(ISEVEN(LEFT(RIGHT(A1,2))),RIGHT(A1)<"5"),--LEFT(A1,LEN(A1)-1),ROUND(A
1,LEN(A1-INT(A1))-3))
Click to expand...
 
Top