Need help in use of IF function

[Santosh080]

Hello Friends

Today my Boss give me some work in excel but i face some problem in it. So i need your help.

I have one excel file like below,


[TABLE="class: outer_border, width: 128, align: left"]
<tbody>[TR]
[TD="width: 64"]Amt[/TD]
[TD="width: 64"]% of Cash[/TD]
[/TR]
[TR]
[TD="align: right"]48000[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD="align: right"]55000[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD="align: right"]32000[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="align: right"]56000[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="align: right"]110000[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD="align: right"]130000[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD="align: right"]250000[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD="align: right"]260000[/TD]
[TD="align: right"]74[/TD]
[/TR]
</tbody>[/TABLE]
















I have given these conditions to apply on the above table.

[TABLE="class: outer_border, width: 393"]
<tbody>[TR]
[TD="colspan: 2, align: center"]Two Conditions
[/TD]
[TD]Outcome after apply of condition[/TD]
[/TR]
[TR]
[TD]if Amt is upto 50000[/TD]
[TD]55% or above[/TD]
[TD]BR[/TD]
[/TR]
[TR]
[TD]if Amt is upto 50001 to 100000[/TD]
[TD]65 % or above[/TD]
[TD]BR[/TD]
[/TR]
[TR]
[TD]if Amt is upto100001 to 200000[/TD]
[TD]75% or above[/TD]
[TD]BR[/TD]
[/TR]
[TR]
[TD]if Amt is upto 200001 to 500000[/TD]
[TD]60% or above[/TD]
[TD]RO
[/TD]
[/TR]
</tbody>[/TABLE]


I can not figure out how to use the IF function or any other function in this situation. I want to apply the TWO conditions so that i get the out come either BR or RO in the adjacent column in the first table.

 

[djmixer135]

Do both conditions on each row need to be true? For example does the Amt need to be <= 500 AND the percentage needs to be 55%+ in order to produce an outcome?

 

[Santosh080]

Yes both conditions need to be true. i want all conditions in one formula. Can it possible?

 

[Special-K99]

Create the following table in a blank sheet, let's call it Sheet 2 starting at A1

0	0	
50000	55%	BR
100000	65%	BR
200000	75%	BR
500000	60%	RO

With your data in Sheet 1 starting at A2

in C2
=INDEX(Sheet2!C$2:C$6,MATCH(A2,IF(B2>Sheet2!B$2:B$6,Sheet2!A$2:A$6),1))

Array formula, use Ctrl-Shift-Enter

 

[Santosh080]

Create the following table in a blank sheet, let's call it Sheet 2 starting at A1

0    0    
50000    55%    BR
100000    65%    BR
200000    75%    BR
500000    60%    RO

With your data in Sheet 1 starting at A2

in C2
=INDEX(Sheet2!C$2:C$6,MATCH(A2,IF(B2>Sheet2!B$2:B$6,Sheet2!A$2:A$6),1))

Array formula, use Ctrl-Shift-Enter

Thank you very much.

In second condition i.e (%) i want this condition 55% and above for all. can it possible. One more thing if the result is #N/A i want to set the result as "HO". Can i do it?

 

[mail2master]

Yes, you can get that easily.... try using IFERROR() function or ISNA() function.

=iferror(INDEX(Sheet2!C$2:C$6,MATCH(A2,IF(B2>Sheet2!B$2:B$6,Sheet2!A$2:A$6),1)),"HO")

=if(isna(INDEX(Sheet2!C$2:C$6,MATCH(A2,IF(B2>Sheet2!B$2:B$6,Sheet2!A$2:A$6),1))),"HO")

 

[Santosh080]

Create the following table in a blank sheet, let's call it Sheet 2 starting at A1

0    0    
50000    55%    BR
100000    65%    BR
200000    75%    BR
500000    60%    RO

With your data in Sheet 1 starting at A2

in C2
=INDEX(Sheet2!C$2:C$6,MATCH(A2,IF(B2>Sheet2!B$2:B$6,Sheet2!A$2:A$6),1))

Array formula, use Ctrl-Shift-Enter

i don't want to use sheet2 in formula. Can it possible without using sheet 2?

 

[Special-K99]

Well youve gotta put the table somewhere...
Decide where you wanna put it and change the formula accordingly.

UPDATE: I'm not sure my formula will work with values "and above", test thoroughly.

By the way if you use 55 instead of 55% then the table should be thie

0	0	
50000	55	BR
100000	65	BR
200000	75	BR
500000	60	RO

UPDATE: I'm not sure my formula will work with values "and above", test thoroughly.

 

[Special-K99]

My formula doesnt work on this selection

32000 32%

What are you expecting the answer to be here?
Its up to 50000 but the percentage is not high enough to produce BR so what are you epexcting the result of this to be as you havent said.

 

[Santosh080]

Two Conditions [TABLE="class: cms_table_outer_border, width: 393"]
<tbody>[TR]
[TD="colspan: 2, align: center"][/TD]
[TD]Outcome after apply of condition[/TD]
[/TR]
[TR]
[TD]if Amt is upto 50000[/TD]
[TD]55% or above[/TD]
[TD]BR[/TD]
[/TR]
[TR]
[TD]if Amt is upto 50001 to 100000[/TD]
[TD]65 % or above[/TD]
[TD]BR


[/TD]
[/TR]
[TR]
[TD]if Amt is upto100001 to 200000[/TD]
[TD]75% or above[/TD]
[TD]BR[/TD]
[/TR]
[TR]
[TD]if Amt is upto 200001 to 500000[/TD]
[TD]60% or above[/TD]
[TD]RO[/TD]
[/TR]
</tbody>[/TABLE]


you can see in formula i need range in amt like
i] 1 to 50000
ii] 500001 to 100000
iii] 100001 to 200000

And in % column i need
i] 55% and above
ii] 65% and above
ii] 75 %and above

all in one formula. is it possible?