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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Try a lookup..

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

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

Hope that helps.
 
Upvote 0
I tried that one, but the rates change monthly, but the data is changed daily. How would look up know what the % range is?
 
Upvote 0
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..
 
Upvote 0
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?
 
Upvote 0
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,"")))
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top