10% Match

[Materialised[Work]]

Hi all,
looking for some help with a spread sheet.
For simplicities sake lets say I have 5 columns. Named:
Invoice Amount
PO Amount
Difference
10% of Invoice Value
Match

Invoice amount is obviously the amount my company has been invoiced
for.
PO Amount is what we calculated our internal Purchase order to be
Difference is the difference between Invoice and the Purchase order.
10% of Invoice Value is literally 10% of the comany invoice value.
Match is where I am having difficulty.

I work in a finance department, and have been asked to produce this
report. Basically if the difference between the invoice amount and the
PO amount is within 10% (above or below) we can pay it.

How would I calculate this using a excel formula? The contents of
Match afterwards should simply be set to "YES" or "NO" as other
functions rely on this output.

Thanks
Mick

 

[Tom Ogilvy]

=if(Abs(Difference)<=10% of Invoice Value,"Yes","No")

so if Difference is in C2 and 10% in D2 in E2

=if(abs(C2)<=D2,"Yes","No")

then drag fill down.

 

[Rog]

Hi Mick

i think you just need an IF function for the Match column,
like this :

=if(abs(DIFFERENCE)<=0.1*(10% OF INVOICE VALUE),"yes","no")

where the capitalised words denote your columns

Rgds

Rog

 

[Stephen Wortley]

Hi Mick

The formula to use is =IF((D1-ABS(C1))>0, "Yes","No")
Where D1 is the cell ref for 10%... and C1 is the Cell ref for
Difference

The way to "read" this is "If the 10% limit minus the difference is
positive (i.e. within limit), then "Yes", else "No"

Hope this helps

Stephen Wortley