This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
In Excel 2003, I am using an IF formula with greater than (>) and less than (<). But I can't get the correct sequence when wanting to say greater than AND less than together in the formula. Please help!
DATA:
P Q R
ROW13 50 6 3000
ROW14 80 9 3500
ROW15 110 12 4000
ROW16 140 15 4500
The problem is as follows: If B29 is less than or equal to 50, then return R13; If B29 is greater than 50 but less than 80, then return R14; If B29 is greater than 80 but less than 110, then return R15; If B29 is greater than 110 but less than 140, then return R16, etc...
The way I see it reading is as follows, but I can't get the correct sequence for the second part:
=IF(B29<=50,R13,IF(AND(B29>50=TRUE,B29<80=TRUE,R14,"0"))
I have searched the net and cannot find any clarification on this type of problem and have tried many different sequences. PLEASE HELP!!!!!
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Hi,
=IF(B29<=50,R13,IF(AND(B29>50,B29<80),R14,"0"))
Regards,
Frank
If this post is the answer to your question, please mark it for all readers as the Answer and helpful!
Hi guys,
Thanks for the help and information...I just love learning more and more in Excel!
Thanks for even noticing the 'etc.' as I didn't know there was a limit to the IF formula, and I have now come to the limit! That means I need to know what else you guys suggest because I don't know much about VLOOKUP! Would this be the best to use for my data or is there something better?
Again...thanks!!
Here is the complete data (related to B29 value returning any of the values relating to column P):
p Q R
ROW 13 50 6 3000
ROW 14 80 9 3500
ROW 15 110 12 4000
ROW 16 140 15 4500
ROW 17 170 18 5000
ROW 18 200 21 5500
ROW 19 230 24 6000
ROW 20 260 27 6500
ROW 21 290 30 7000
ROW 22 320 33 7500
ROW 23 350 36 8000
ROW 24 380 39 8500
ROW 25 410 42 9000
ROW 26 440 45 9500
ROW 27 470 48 10000
ROW 28 500 51 10500
Lyndsweb wrote:
In Excel 2003, I am using an IF formula with greater than (>) and less than (<). But I can't get the correct sequence when wanting to say greater than AND less than together in the formula. Please help!
DATA:
P Q R
ROW13 50 6 3000
ROW14 80 9 3500
ROW15 110 12 4000
ROW16 140 15 4500
The problem is as follows: If B29 is less than or equal to 50, then return R13; If B29 is greater than 50 but less than 80, then return R14; If B29 is greater than 80 but less than 110, then return R15; If B29 is greater than 110 but less than 140, then return R16, etc...
Arguably, it would be better to use VLOOKUP. But you do not provide sufficient information to reconstruct things so that VLOOKUP will work exactly as you specify, unless we make some assumptions.
The IF expression can be:
=IF(B29<=50,R13,IF(B29<=80,R14,IF(B29<=110,R15,IF(B29<=140,R16,R17))))
I am not sure what "etc..." means. Presumably, you have more range limits to test; I assume just one more (R17). But that is why VLOOKUP is a better solution. Note that with Excel 2003, you are limited to 8 range limits -- 7 nested IF expressions if you use no other functions.
You do not need to include the AND and the test for B29>50 in that second IF... if B29 did not meet the <=50 condition, then it is automatically >50, so you do not have to test for that. Try this formula...
=IF(B29<=50,R13,IF(B29<80,R14,IF(B29<110,R15,IF(B29<140,R16,"B29 is greater than 140"))))
Note: I have some concern about the "etc." that you ended your sequence with... there is a limit as to how many IF statements can be nested within each other.
NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.
Woohoo!!!!! It works! And it is so easy and obvious!! Can't believe I missed that sequence completely!
Thank you very much!