Rounding

[squirrel74]

I need a formula that will only round if the decimal of the result is greater
than something ##.5, otherwise I don't want the formula to round. Below is an
example of what I've developed so far, but I need to know how to enter in a
value into the formula to indicate a number with a decimal greater than .5

=IF(AVERAGE(B1:D1)>=##.5,ROUND(AVERAGE(B1:D1),0),AVERAGE(B1:D1))

Ultimately what the formula will needs to do is average 3 numbers, rounding
up only if the resulting decimal value is greater than .5, otherwise, the
formula should not round at all.

Example:
When I enter the following numbers the results will round due to the decimal
being greater than or equal to .5
(90 + 76 + 70)/3 = 78.6667
Result to display will be 79

However if I enter the following numbers the results will not round due to
the decimal being less than .5
(85 + 80 + 70)/3 = 78.3333
Result to display will be 78.3333

Thank you to anyone that can help.

[Richard Buttrey]

On Thu, 2 Mar 2006 07:46:32 -0800, "squirrel74"
<[email protected]> wrote:

>I need a formula that will only round if the decimal of the result is greater
>than something ##.5, otherwise I don't want the formula to round. Below is an
>example of what I've developed so far, but I need to know how to enter in a
>value into the formula to indicate a number with a decimal greater than .5
>
>=IF(AVERAGE(B1:D1)>=##.5,ROUND(AVERAGE(B1:D1),0),AVERAGE(B1:D1))
>
>Ultimately what the formula will needs to do is average 3 numbers, rounding
>up only if the resulting decimal value is greater than .5, otherwise, the
>formula should not round at all.
>
>Example:
>When I enter the following numbers the results will round due to the decimal
>being greater than or equal to .5
>(90 + 76 + 70)/3 = 78.6667
>Result to display will be 79
>
>However if I enter the following numbers the results will not round due to
>the decimal being less than .5
>(85 + 80 + 70)/3 = 78.3333
>Result to display will be 78.3333
>
>Thank you to anyone that can help.

If I've understood you correctly, a simple modification to your
formula is shown below. A1 holds the value of the decimal test value,
e.g. 0.5 in your example.

=IF(AVERAGE(B1:D1)-INT(AVERAGE(B1:D1))>=A1,ROUND(AVERAGE(B1:D1),0),AVERAGE(B1:D1))

HTH

Richard Buttrey
__

[CLR]

Maybe....

=IF((A1-INT(A1))>=0.5,ROUNDUP(A1,0),A1)

Vaya con Dios,
Chuck, CABGx3



"squirrel74" wrote:

> I need a formula that will only round if the decimal of the result is greater
> than something ##.5, otherwise I don't want the formula to round. Below is an
> example of what I've developed so far, but I need to know how to enter in a
> value into the formula to indicate a number with a decimal greater than .5
>
> =IF(AVERAGE(B1:D1)>=##.5,ROUND(AVERAGE(B1:D1),0),AVERAGE(B1:D1))
>
> Ultimately what the formula will needs to do is average 3 numbers, rounding
> up only if the resulting decimal value is greater than .5, otherwise, the
> formula should not round at all.
>
> Example:
> When I enter the following numbers the results will round due to the decimal
> being greater than or equal to .5
> (90 + 76 + 70)/3 = 78.6667
> Result to display will be 79
>
> However if I enter the following numbers the results will not round due to
> the decimal being less than .5
> (85 + 80 + 70)/3 = 78.3333
> Result to display will be 78.3333
>
> Thank you to anyone that can help.

[JE McGimpsey]

One way:

=ROUND(AVERAGE(B1:D1),15 - 15*(MOD(AVERAGE(B1:D1),1)>=0.5))

In article <[email protected]>,
"squirrel74" <[email protected]> wrote:

> I need a formula that will only round if the decimal of the result is greater
> than something ##.5, otherwise I don't want the formula to round. Below is an
> example of what I've developed so far, but I need to know how to enter in a
> value into the formula to indicate a number with a decimal greater than .5
>
> =IF(AVERAGE(B1:D1)>=##.5,ROUND(AVERAGE(B1:D1),0),AVERAGE(B1:D1))
>
> Ultimately what the formula will needs to do is average 3 numbers, rounding
> up only if the resulting decimal value is greater than .5, otherwise, the
> formula should not round at all.
>
> Example:
> When I enter the following numbers the results will round due to the decimal
> being greater than or equal to .5
> (90 + 76 + 70)/3 = 78.6667
> Result to display will be 79
>
> However if I enter the following numbers the results will not round due to
> the decimal being less than .5
> (85 + 80 + 70)/3 = 78.3333
> Result to display will be 78.3333
>
> Thank you to anyone that can help.