Need nested if formula

[Excelmasters]

Hello All,

I need a Nested if and am sure i will get some help here.

My answer should be in cell b20

My criteria is

Note difference amount is in B19






Excel 2010

	D	E	F	G	H
if Invoice amount is less than
different amount can be	10 or lesser
if Invoice amount is between	<500 to 1000>
different amount can be	50 or lesser
if Invoice amount is more than
different amount can be	100 or lesser
if any condition matches it should be "ok to process" or else "Need to work"

<tbody>
[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Excel 2010

	B	C
Receipt$
CRI Letter$
Receipt Total$
Receipt plus Letter$
Diff amt$

<tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: #538DD5, align: center"]Invoices Less than 500[/TD]
[TD="bgcolor: #538DD5, align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #EEECE1"]Invoice Amount$[/TD]
[TD="bgcolor: #92D050, align: right"] $ 500.00[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] $ 10,000.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 5,000.00[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 3,000.00[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"] $ 1,250.00[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"] $ 18,000.00[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"] $ 19,250.00[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"] $ 10.00[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]ok to process[/TD]
[TD="align: center"][/TD]

[TD="align: center"]21[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Data​

 

[gaz_chops]

so you mean + or - 10, 50, 100?

 

[gaz_chops]

If it is try

=IF(OR(C19>LOOKUP(C3,{0,500,1000},{10,50,100}),C19<-LOOKUP(C3,{0,500,1000},{10,50,100})),"Need to work","ok to process")

 

[Excelmasters]

Am Really sorry for bothering you.

still am not getting. i will try to explain this in accounting terms.

Invoice is our supplier is asking money for the goods. and receipt is what we got from supplier.

we will match invoice amount and receipt amount. if the i am deducting receipt amount from invoice amount.

see if invoice amount is more and receipt value is less the diffrence will come in + value so it means this is loss for us. then if the diffrenence is +1 value also i need to say as "work on this"

and diffrence value is in - values then its fine its income for us.. but we should not cheat our suppliers. hence. if the diffrence value is -10 and less for $ 500 invoices its ok not more than that same way other condtions.

hope the above words are helpful

 

[gaz_chops]

Not sure, but try

=IF(OR(AND(C19>0,C19<=LOOKUP(C3,{0,500,1000},{10,50,100})),AND(C19<0,C19>=-LOOKUP(C3,{0,500,1000},{10,50,100}))),"ok to process","Need to work")

Or a better version

=IF(ABS(C19)<=LOOKUP(C3,{0,500,1000},{10,50,100}),"ok to process","Need to work")

 

[Excelmasters]

yes its working just need small changes.

when the diffrence amount is in positive value.. i mean c19 is any value if not minus.. it should say "need to work"

and first condtion is not working.. ( if the invoice value is less than 500 and diffrence value is -10 it should say ok to process. currently upto -10 saying ok to process and saying ok to process for more than -10 as well.

please help

 

[Excelmasters]

can anyone help in this please...

 

[gaz_chops]

Try

=IF(OR(C19>0,C19<-LOOKUP(C3,{0,500,1000},{10,50,100})),"Need to work","ok to process")

 

[Excelmasters]

Gaz,

Am so happy.. you just got me a appreciation email from my boss..

thanks a lot for your help.

 

[gaz_chops]

You're welcome, glad we got there