# How to - If A is greater than X but less than Y, return Z

1. ## How to - If A is greater than X but less than Y, return Z

How to write a formula that says:

=IF(L25<8,8,IF(L25>8 but less than 10,10,IF(L25>10 but less than 12,12,0)))  Register To Reply

2. ## Re: How to - If A is greater than X but less than Y, return Z

Try it like this:

=IF(L25<8,8,IF(L25<10,10,IF(L25<12,12,0)))

Hope this helps.

Pete  Register To Reply

3. ## Re: How to - If A is greater than X but less than Y, return Z

Or a lookup function based on a lookup table sorted in descending order.
Lookup table: ``Please Login or Register  to view this content.``
Then the formula could be =INDEX(2nd_column_lookup_table,MATCH(L25,1st_column_lookup_table,-1)). To handle the cases when L25 is greater than 12, nest inside of an IFERROR() or IFNA() function =IFERROR(INDEX(...),0).

Will something like that work?  Register To Reply

4. ## Re: How to - If A is greater than X but less than Y, return Z

I thought about it for awhile and came up with this formula, which worked until I added my last if argument. Now it says there's a problem with the formula but no clue what the mistake is.

=IF(L25<5,10,IF(AND(L25>5,L25<=6,12,IF(AND(L25>6,L25<=8,8,IF(AND(L25>8,L25<=10),10,IF(AND(L25>10,L25<=12),12,0)))))  Register To Reply

5. ## Re: How to - If A is greater than X but less than Y, return Z

for yours you are missing a couple parentheses here ...
=IF(L25<5,10,IF(AND(L25>5,L25<=6),12,IF(AND(L25>6,L25<=8),8,IF(AND(L25>8,L25<=10),10,IF(AND(L25>10,L25<=12),12,0)))))
Now, you don't need all those AND statements, if(L25<5,10,IF(L25<=6,12,IF(L25<=8,8
the point is that each statement is in and of itself enough, for example less than 5 will go first, 5 to 6 will go next, 6.0001 to 8 next, IF/THEN statements work from left to right and work until they find something that satisfies them, that is why Pete gave you what he gave you. So all those ANDs are not necessary.
Hope that helps.

Oh, and you aren't dealing with 5 in L25, you have less than 5 and greater than 5 and less than or equal to 6 so a 5 will return zero.  Register To Reply

6. ## Re: How to - If A is greater than X but less than Y, return Z

Sambo kid,

Thank you very much. Those 2 parentheses worked like a charm. Can't believe I missed that, but by the time I sent that last post I had been staring at formulas for 6 hours, about brain dead.

Concerning your subsequent explanation, I want to make sure I understand it correctly. Formulas read one argument at a time from left to right. So when it comes to the first argument that is true, it returns the correct result and does not continue to check the other arguments. Therefore, if I leave out the "between this and that" arguments from my formula and just say "if less than this", and L25 equals 4, the formula will not recognize that all arguments are true and throw up an error message. It will return the result for the first argument. Is that an accurate?  Register To Reply

7. ## Re: How to - If A is greater than X but less than Y, return Z

MrShorty,

Thank you very much for your answer, but those formulas are a little above my pay grade. Not up to speed on the lookup tables.  Register To Reply

8. ## Re: How to - If A is greater than X but less than Y, return Z Originally Posted by Pete_UK Try it like this:

=IF(L25<8,8,IF(L25<10,10,IF(L25<12,12,0)))

Hope this helps.

Pete
Pete,

Thank you very much for your response. At first, I didn't think your formula would work for what I needed because each argument didn't give a range of "between this and that". For instance, I thought if L25 equaled say 5, the formula would find all arguments true and return an error. But Sambo kid explained that the formula reads left to right and stops on the first one that is true. Now, I understand why your suggested formula would in fact work. Thank you again for your help.  Register To Reply

9. ## Re: How to - If A is greater than X but less than Y, return Z

lasc to post #6, yes that is correct so for correct results you dont want to start less than 12 then 10 because even a 4 will be less than 12 so that would satisfy the formula but return a result you dont want so you usually make them progressive from the left.  Register To Reply

10. ## Re: How to - If A is greater than X but less than Y, return Z

Pete  Register To Reply