You nest them inside each other. So something like this:
=if({cell}>25,5,if({cell}>20,4,if({cell}>15,3,if({cell}>10,2,1))))
Answer from sean108 on community.spiceworks.comVideos
You nest them inside each other. So something like this:
=if({cell}>25,5,if({cell}>20,4,if({cell}>15,3,if({cell}>10,2,1))))
We have a speadsheet that we use for our sales tracking
It tracks how many cars they’ve sold, how many people they’ve talked to etc etc.
One thing they would like to see is how they get to the next level (an internal number that shows how well they’re doing, how much commission they are making etc)
I want to put this in the excel sheet but I can’t figure out how to put multiple if statements into the cell where I want it to display.
So it breaks down like this.
It’s a 3 month average (I have that part down)
What I need it to display is if their 3 month average is less than 11 units sold, it’s a 1
Level 2 is 11.1 to 16 Units
Level 3 is 16.1 to 20 Units
Level 4 is 20.1 to 25 units
and level 5 is 25 and above.
HALP!
Here is a formula you could use:
=IF(SUMPRODUCT(--(LEFT(G3,1)={"1","2","3"}))>0,"998",IF(SUMPRODUCT(--(LEFT(G3,1)={"4","5"}))>0,"996",IF(AND(SUMPRODUCT(--(LEFT(G3,1)="9"))>0,SUMPRODUCT(--(LEFT(K3,3)={"075","076","089"}))=0),"997")))
If K3 is an actaul number and not text than @ScottCraner has a very good point and it won't see the leading zero. To overcome this you should add ' in front of value in cell K3.
To keep it in your existing style of nested if:
=IF(LEFT(G3,1)="1","998",IF(LEFT(G3,1)="2","998",IF(LEFT(G3,1)="3","998",IF(LEFT(G3,1)="4","996",IF(LEFT(G3,1)="5","996",IF(AND(LEFT(G3,1)="9",NOT(OR(OR(LEFT(K3,3)="075",LEFT(K3,3)="076"),LEFT(K3,3)="089"))),"997",K3))))))
=
IF(LEFT(G3,1)="1","998",
IF(LEFT(G3,1)="2","998",
IF(LEFT(G3,1)="3","998",
IF(LEFT(G3,1)="4","996",
IF(LEFT(G3,1)="5","996",
IF(
AND(
LEFT(G3,1)="9",
NOT(
OR(
OR(
LEFT(K3,3)="075",
LEFT(K3,3)="076"),
LEFT(K3,3)="089"
)
)
), "997", K3
)
)
)
)
)
)
Perhaps better to avoid nested IF's and make use of one of Excel's lookup functions like XLOOKUP.
Let's assume that the Summary sheet contains data like in the picture below:
The this will work in G5 (can be copied down):
An IF function only has three parts. For all apart from the last one, you don't want the "" part - you want to perform the next IF, so:
=IF(F5="Keller Must Pay-(Non Negotiable)",B5*Summary!$K$3,IF(F5="Keller Must Pay-(Amount Negotiable)",B5*Summary!$K$4,IF(F5="Keller should pay-(No contract Justification)",B5*Summary!$K$5,IF(F5="We would like Keller to pay",B5*Summary!$K$6,IF(F5="Stretch Target",B5*Summary!$K$7,"")))))