Format Numbers in IIF statement

[alecgreen]

Hi, I am using the following IIF statement to calculate the percentage
difference between a Selling Price and a Order Selling Price, bearing
in mind you cannot deivide by zero!

OrdvsSell: IIf([SellingPrice]=0,"NO
CALC",(([OrderSellingPrice]-[SellingPrice])/[SellingPrice]*100))

However I cannot format the result to say 2 decimal places, instead of
the actual calculated result - Can this be done within the IIF
statement?


Thanks

Alec

 

[Marshall Barton]

Hi, I am using the following IIF statement to calculate the percentage
difference between a Selling Price and a Order Selling Price, bearing
in mind you cannot deivide by zero!

OrdvsSell: IIf([SellingPrice]=0,"NO
CALC",(([OrderSellingPrice]-[SellingPrice])/[SellingPrice]*100))

However I cannot format the result to say 2 decimal places, instead of
the actual calculated result - Can this be done within the IIF
statement?

The problem probably is that the result is being converted
to a string because of the possible "NO CALC" result.

Try this:

OrdvsSell: IIf([SellingPrice]=0,"NO
CALC",Format(([OrderSellingPrice]-[SellingPrice])/[SellingPrice]),
"0.00%" )

 

[Jeff L]

Yes.
OrdvsSell: IIf([SellingPrice]=0,"NO
CALC",Round((([OrderSellingPrice]-[SellingPrice])/[SellingPrice]*100),2))
This will round your result to 2 decimal places.