10 percent increase between fields formula

I

Irishimp23

I have a query which contains a "Curr_Amount" column that has numbers, a
“Prev_Amount " column that has numbers and also a"Difference" column that is
blank. If the “Curr_Amount†increases or decreases from the “Prev_Amount†by
10% ONLY... I need to note it in the blank “Difference†column. I need help
with devising a formula to calculate if there is a 10 % increase or decrease…
but if the percentage is higher or lower than 10% I need the line in the
“Difference†column to be null. I hope I explained this correctly and I
appreciate any assistance.
 
S

Smartin

Irishimp23 said:
I have a query which contains a "Curr_Amount" column that has numbers, a
“Prev_Amount " column that has numbers and also a"Difference" column that is
blank. If the “Curr_Amount†increases or decreases from the “Prev_Amount†by
10% ONLY... I need to note it in the blank “Difference†column. I need help
with devising a formula to calculate if there is a 10 % increase or decrease…
but if the percentage is higher or lower than 10% I need the line in the
“Difference†column to be null. I hope I explained this correctly and I
appreciate any assistance.
Click to expand...

Hello,

For starters, we could try nested IIf expressions to see if Curr is
equal to Prev +/- 10%:

Difference: IIf (Curr = Prev * 1.1, "10% increase",
IIf (Curr = Prev * 0.9, "10% decrease", Null),
Null)

The big caveat here is the decimal math may produce incorrect results.
We might be able to wrap some kind of tolerance around the math to
tighten it up. For this, it would help to know something about the
domain (double, single, integer...) and range (positive, negative, order
of magnitude) of the Curr/Prev values you expect to look at.

Maybe there are other ideas as well.
 
Top