Exception report in excel

[jj]

I'm struggling to create an exception report.
I need to flag an empty cell if the adjacent cell varies more than 5% of
another cell.
Example:
C2 is $100
D2 is $300
I need E2 to post a * indicating D2 is more than 5% of C2
Any help would be appreciated.

 

[Earl Kiosterud]

jj,

If you want a flag only if it's 5% over:
=IF((D4/C4-1)>5%,"*","")

If you want a flag for more than plus or minus 5%:
=IF(ABS(D2/C2-1)>5%,"*","")

 

[Michael]

Hi JJ
Try this for more than or less than 5%

=IF(D2>(C2*1.05),"Flag",IF(D2<(C2*0.95),"Flag",""))

Change the "Flag" text to suit yourself.

HTH
Michael

 

[Michael]

Hi JJ
This formula will identify if D2 is 5% greater than C2
=IF(D2>(C2*1.05),"Flag","")
This one does both greater than or Less than 5%

=IF(D2>(C2*1.05),"Flag",IF(D2<(C2*0.95),"Flag",""))

You can change the "Flag" text to whatever you like.

HTH
Michael

 

[jj]

Thanks Michael!!!!

Hi JJ
Try this for more than or less than 5%

=IF(D2>(C2*1.05),"Flag",IF(D2<(C2*0.95),"Flag",""))

Change the "Flag" text to suit yourself.

HTH
Michael

 

[jj]

Thanks Earl!

jj,

If you want a flag only if it's 5% over:
=IF((D4/C4-1)>5%,"*","")

If you want a flag for more than plus or minus 5%:
=IF(ABS(D2/C2-1)>5%,"*","")