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
5	if Invoice amount is less than		500
6	different amount can be		10 or lesser
7
8
9	if Invoice amount is between		<500 to 1000>
10	different amount can be		50 or lesser
11
12	if Invoice amount is more than		1000
13	different amount can be		100 or lesser
14
15	if any condition matches it should be "ok to process" or else "Need to work"

<tbody>
</tbody>

Sheet1​

Excel 2010

	B	C
2	Invoices Less than 500
3	Invoice Amount$	$ 500.00
4	Receipt$	$ 10,000.00
5		$ 5,000.00
6		$ 3,000.00
7
8
9
10
11
12
13
14
15	CRI Letter$	$ 1,250.00
16
17	Receipt Total$	$ 18,000.00
18	Receipt plus Letter$	$ 19,250.00
19	Diff amt$	$ 10.00
20	ok to process
21
22

<tbody>
</tbody>

Data​

 

[Special-K99]

You need 2 formulas, one for "different amount" and one for the result "ok to process" or "need to work" ?

Different Amount
=LOOKUP(C3,{0,500,1000},{10,50,100})

I dont understand this bit

if any condition matches it should be "ok to process" or else "Need to work"

You've covered ALL the possibilities, so one of them conditions will ALWAYS match hence the result will always be "OK to process"

 

[Excelmasters]

Hi Special,

thanks for the help.

can't we have both the cell condition and get result.

this is for audit purpose. when there is a diffrence amount comes between two accounts we need to check what is the invoice amount.

as i said early. if invoice amount is less than $500 and diffrence amount is less than or equal to 10 then its ok or else we need to work on that same way other conditions.

if invoice amount is between 500 to 1000 and diffrence can be 50 or lesser .

if invoice amount is more than $ 1000 diffrence amount can be upto $100 if diffrence crosses the limit we need to work.

can we have all conditions in one nested if.?

thanks

 

[gaz_chops]

Would it be

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

 

[Excelmasters]

Gaz,

You are just awesome.

Thank you so much

 

[gaz_chops]

You're welcome.

 

[Excelmasters]

Gaz,

i came back again.

iam sorry...can we make it the same condition in minus values.. i mean if the diffrence amount is in Minus then this it should be "ok to process" or else need to work.

that too... if the minus values should not go down.. example.. -10 ok not -11 and -50 ok not -51 -100 ok not -101

 

[Excelmasters]

i mean can we change the same thing to the minus values

 

[gaz_chops]

Think I understand, try

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

 

[Excelmasters]

No am not getting..

its simple..

the same condtion. if the diffrence value is in minus value (my criteria such as -10, -50 ,-100) should be ok to process..or else if diffrence is $1 in (positive+) also it should be need to work