Nested If Statements with numerous conditions

Gregor77

New Member
Joined
Dec 9, 2010
Messages
19
The new boss gave me a bit of a challange here....

Using excel 2003 (SP3)

Cell "I6" is a calc dividing a retail target and how the person performed giving me a %. example 158%. Then the person belongs to a catagory eg: 1,2,3,4. Each catagory has a % target and a $$$ value assigned to the target.

Here is what I put together, but it will only calc the first incentive... if the % in "I6" changes to like 500%, the value is still the same? I did something wrong!

=IF(I6>='Incentives'!B3,'Incentives'!C3,IF(I6>='Incentives'!B4,'Incentives'!C4))

B3=100% on another tab
B4= $400

And so on...

Ideally it would be great if it was more like: if the person is catagory 1, and they are = or greater than 100% then apply incentives B3 (which is the $$$ value if over a certain %), then if the person is 125% then apply Incentive C3, if over 150% then apply INCentive C4.

I would just like to see what the $$$ is if someone is at a certain %.

Any help would be much appreciated!!!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try a lookup..

=LOOKUP(I6,'Incentives'!B3:C4)

Be sure the data on Incentives, is sorted Ascending by column B.

Hope that helps.
 

Gregor77

New Member
Joined
Dec 9, 2010
Messages
19
I tried that one, but the rates change monthly, but the data is changed daily. How would look up know what the % range is?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
I'm not sure I understand the problem

If the rates change, I presume they are changed in the B3:C4 range..
Then the formula would be using the updated rates..
 

Gregor77

New Member
Joined
Dec 9, 2010
Messages
19

ADVERTISEMENT

Yes, it does work, thanks! I didn't realise that it adjusted based on the rates on the main page. But how do I get the "Lookup" to function if nested?

=IF(D6="1",LOOKUP(I6,'Incentives'!$B$2:$C$4,IF(D6="2",LOOKUP(I6,' Incentives'!$B$8:$C$10))))

Would this work?
 

iDeals

Board Regular
Joined
Oct 22, 2008
Messages
236
Code:
=if(and(i6>=0.1,i6<0.125),incentives!b3,if(and(i6>=0.125,i6<0.15),incentives!c3,if(i6>=0.15,incentives!d3,"")))
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

How many options are there for D6?
And is D6 literally just a number 1 or 2 or 3 etc?

You can try

=LOOKUP(I6,CHOOSE(D6,'Incentives'!$B$2:$C$4,'Incentives'!$B$8:$C$10))

The choose part can handle up to 29 different ranges based on the value of D6 (1 through 29)
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Also, if your lookup ranges are consistently spaced, ie every 6 rows like your example
B2:C4 then 6 rows down (from B2) is B8:C10, then 6 rows down (From B8) is B14:C16 etc...

Or another similar pattern as long as it's consistent..

Then you can do this

=LOOKUP(I6,OFFSET('Incentives'!$B$2:$C$4,(D6-1)*6))

The 6 is the number of rows between the first row of each range (2,8,14,20)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top