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)))
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)))
Try it like this:
=IF(L25<8,8,IF(L25<10,10,IF(L25<12,12,0)))
Hope this helps.
Pete
How about =MAX(8,CEILING(L25,2))?
Or a lookup function based on a lookup table sorted in descending order.
Lookup table: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).Please Login or Register to view this content.
Will something like that work?
Originally Posted by shg
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)))))
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.
Last edited by Sam Capricci; 12-11-2019 at 09:13 PM.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
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?
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.
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.
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.
If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks