Format Numbers in IIF statement

  • Thread starter Thread starter alecgreen
  • Start date Start date
A

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
 
alecgreen said:
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?
Click to expand...


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%" )
 
Yes.
OrdvsSell: IIf([SellingPrice]=0,"NO
CALC",Round((([OrderSellingPrice]-[SellingPrice])/[SellingPrice]*100),2))
This will round your result to 2 decimal places.
 
Back
Top