Rounding
 

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),A VERAGE(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.

Rounding
 

On Thu, 2 Mar 2006 07:46:32 -0800, "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.

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),AV ERAGE(B1:D1))

HTH

Richard Buttrey
__

Rounding
 

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),A VERAGE(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.

Rounding
 

One way:

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

In article ,
"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),A VERAGE(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.