Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
10% Match
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
10% Match
=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. -- Regards, Tom Ogilvy "Materialised[Work]" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
10% Match
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 -----Original Message----- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
searching a list box for a filename match...and highlighting the match | Excel Programming |