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%,"*","")

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"jj" <jj@discussions.microsoft.com> wrote in message
news:8F77D654-B682-4330-9E86-43E552CE3254@microsoft.com...
> 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.

[jj]

Thanks Earl!

"Earl Kiosterud" wrote:

> 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%,"*","")
>
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "jj" <jj@discussions.microsoft.com> wrote in message
> news:8F77D654-B682-4330-9E86-43E552CE3254@microsoft.com...
> > 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.
>
>
>

[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" wrote:

> 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.

[jj]

Thanks Michael!!!!

"Michael" wrote:

> 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" wrote:
>
> > 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.

[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" wrote:

> 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.