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?

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

"James" wrote in message
...
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?

the numbers would range from about .50000 to 1.9000

"T. Valko" wrote:

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

"James" wrote in message
...
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?

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)


"James" wrote in message
...
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?

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))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"James" wrote in message
...
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?

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.

"James" wrote:

the numbers would range from about .50000 to 1.9000

"T. Valko" wrote:

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

"James" wrote in message
...
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?

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(MO D(A2,1),5,1)+0,2),ROUND(A2,3),TRUNC(A2,3))),A2)

Biff

"James" wrote in message
...
the numbers would range from about .50000 to 1.9000

"T. Valko" wrote:

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

"James" wrote in message
...
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?

0.5 = #VALUE!
1 = 0
0.25 = 0.2
1.5409 = 1.541

Biff

"Bob Phillips" wrote in message
...
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)


"James" wrote in message
...
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?

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

Biff

"Ragdyer" wrote in message
...
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))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"James" wrote in message
...
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?

http://groups.google.com/group/micro...7fce6145b70d69

Jerry

"James" wrote:

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?

Thank you, it worked very well. I could not have done it on my own.

"Bob Phillips" wrote:

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)


"James" wrote in message
...
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?