Rounding Issues in Report Calculations

[RosyGlasses]

I have three summary calculations in a report, formatted as 0.00%. The IIF statements in Calcs 2 & 3 are to ignore nulls and zeroes in the averaging of the detail, and return N/A when there are only nulls or zeroes in the detail.

Calc 1 (accuracy):
=((Sum(Fields!AuditID.Value)- Sum(Fields!FormErrorTotal.Value))/Sum(Fields!AuditID.Value))

Calc2 (goal):
=(IIf(IsNothing(count(Fields!FormGoal.Value)), "N/A",
IIf((sum(iif(Fields!FormGoal.Value>0 , 0, 1))/count(Fields!FormGoal.Value)= 1) , "N/A",
sum(Fields!FormGoal.Value)/sum(iif(Fields!FormGoal.Value<=0 , 0, 1))/100)))

Calc3 (variance between accuracy and goal):
=IIf(IsNothing(count(Fields!FormGoal.Value)), "N/A",
IIf((sum(iif(Fields!FormGoal.Value>0 , 0, 1))/count(Fields!FormGoal.Value)= 1) , "N/A",
(Sum(Fields!AuditID.Value)- Sum(Fields!FormErrorTotal.Value))/Sum(Fields!AuditID.Value) -
sum(Fields!FormGoal.Value)/sum(iif(Fields!FormGoal.Value<=0 , 0, 1))/100))

Because I am rounding to two decimal places, the variance sometimes appears wrong by .01%. If I export the report to Excel I can see the variance is correct, but when rounded to the hundreths, appears wrong on the printed report.

Is it possible to truncate the data returned in the first two summary calculations to the hundreths? For example, if a number returned in calculation 1 or 2 is 99.8765, I want it to be 99.88 (or 99.87, either one). I don’t want to just see it displayed that way, I want the actual data to be 99.88.

I have played with ROUND(formula,2) endlessly but it appears to round only to the closest integer, not hundreths.

Any suggestions would be greatly appreciated. Thank you. Rosy

 

[xlbo]

Have you got your field formatted as N2 by any chance?

N2 applies a mask to the data but does not actually change it

ROUND(formula,2) should round to 2 decimal places

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question