SOLVED

Problem with nested IF formula #Excel #2018

%3CLINGO-SUB%20id%3D%22lingo-sub-229708%22%20slang%3D%22en-US%22%3EProblem%20with%20nested%20IF%20formula%20%23Excel%20%232018%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-229708%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20formula%20that%20will%20check%20the%20value%20in%20the%20adjacent%20cell%20to%20determine%20if%20it%20falls%20within%20a%20specified%20range%2C%20and%20if%20so%2C%20return%20text%20for%20the%20range%2C%20i.e%2C%205-9.%20This%20is%20the%20formula%20I%20wrote%3A%3C%2FP%3E%3CP%3E%3DIF(P12%26lt%3B5%2C%220-4%22%2CIF(and(P12%26gt%3B4%2Cp12%26lt%3B10%2C%225-9%22%2CIF(and(P12%26gt%3B9%2Cp12%26lt%3B15%2C%2210-14%22%2CIF(and(P12%26gt%3B14%2Cp12%26lt%3B20%2C%2215-19%22%2CIF(P12%26gt%3B%3D20%2C%2220%2B%22%2C%22%22))))))))%3C%2FP%3E%3CP%3EI%20am%20getting%20the%20'there's%20a%20problem%20with%20this%20formula'%20message.%20When%20I%20click%20OK%20to%20check%20where%20the%20problem%20lies%2C%20the%20cursor%20points%20to%20the%20place%20after%20the%20second%20closing%20paragraph.%20I%20know%20the%20number%20of%20opening%20and%20closing%20paragraphs%20match.%20I%20can't%20figure%20out%20what's%20wrong.%20Any%20help%20is%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-229708%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENested%20If%20statement%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-233727%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20nested%20IF%20formula%20%23Excel%20%232018%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-233727%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-233624%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20nested%20IF%20formula%20%23Excel%20%232018%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-233624%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20is%20very%20efficient.%20I%20appreciate%20both%20answers!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-233622%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20nested%20IF%20formula%20%23Excel%20%232018%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-233622%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Phillip.%20This%20works%20also.%20I%20appreciate%20the%20explanation.%20I%20have%20a%20better%20understanding%20now%20of%20how%20such%20statements%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-233621%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20nested%20IF%20formula%20%23Excel%20%232018%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-233621%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei.%20This%20worked%20like%20a%20charm!%20Thanks%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-229832%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20nested%20IF%20formula%20%23Excel%20%232018%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-229832%22%20slang%3D%22en-US%22%3E%3CP%3Eor%20use%20Sergei's%20answer%20which%20is%20even%20better..%3C%2FP%3E%3CDIV%20class%3D%22user-login%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-229830%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20nested%20IF%20formula%20%23Excel%20%232018%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-229830%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20haven't%20closed%20the%20ANDs%3C%2FP%3E%3CP%3Eyou%20formula%20should%20be%3A%3C%2FP%3E%3CP%3E%3DIF(P12%26lt%3B5%2C%220-4%22%2CIF(AND(P12%26gt%3B4%2CP12%26lt%3B10%3CSTRONG%3E)%3C%2FSTRONG%3E%2C%225-9%22%2CIF(AND(P12%26gt%3B9%2CP12%26lt%3B15%3CSTRONG%3E)%3C%2FSTRONG%3E%2C%2210-14%22%2CIF(AND(P12%26gt%3B14%2CP12%26lt%3B20%3CSTRONG%3E)%3C%2FSTRONG%3E%2C%2215-19%22%2CIF(P12%26gt%3B%3D20%2C%2220%2B%22%2C%22%22)))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20actually%20don't%20really%20need%20the%20ANDs%20at%20all..%20this%20will%20do%20the%20same%20thing%3A%3C%2FP%3E%3CP%3E%3DIF(P12%26lt%3B5%2C%220-4%22%2CIF(P12%26lt%3B10%2C%225-9%22%2CIF(P12%26lt%3B15%2C%2210-14%22%2CIF(P12%26lt%3B20%2C%2215-19%22%2C%2220%2B%22))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20works%20because%20excel%20will%20stop%20at%20the%20first%20TRUE%20condition.%20If%20P12%20is%209%20for%20example%2C%20you%20dont%20need%20the%20AND%20to%20check%20if%20its%20'%26gt%3B4'%2C%20if%20it%20less%20less%20than%205%20the%20first%20IF%20would%20have%20been%20true.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-229829%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20nested%20IF%20formula%20%23Excel%20%232018%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-229829%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Laurie%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%3C%2FP%3E%0A%3CPRE%3E%3DLOOKUP(P12%2C%7B0%2C5%2C10%2C15%2C21%7D%2C%7B%220-4%22%2C%225-9%22%2C%2210-14%22%2C%2215-19%22%2C%2220%2B%22%7D)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Greetings,

I am trying to create a formula that will check the value in the adjacent cell to determine if it falls within a specified range, and if so, return text for the range, i.e, 5-9. This is the formula I wrote:

=IF(P12<5,"0-4",IF(and(P12>4,p12<10,"5-9",IF(and(P12>9,p12<15,"10-14",IF(and(P12>14,p12<20,"15-19",IF(P12>=20,"20+",""))))))))

I am getting the 'there's a problem with this formula' message. When I click OK to check where the problem lies, the cursor points to the place after the second closing paragraph. I know the number of opening and closing paragraphs match. I can't figure out what's wrong. Any help is appreciated.

7 Replies
Best Response confirmed by Laurie McDowell (New Contributor)
Solution

Hi Laurie,

 

You may use

=LOOKUP(P12,{0,5,10,15,21},{"0-4","5-9","10-14","15-19","20+"})

You haven't closed the ANDs

you formula should be:

=IF(P12<5,"0-4",IF(AND(P12>4,P12<10),"5-9",IF(AND(P12>9,P12<15),"10-14",IF(AND(P12>14,P12<20),"15-19",IF(P12>=20,"20+","")))))

 

You actually don't really need the ANDs at all.. this will do the same thing:

=IF(P12<5,"0-4",IF(P12<10,"5-9",IF(P12<15,"10-14",IF(P12<20,"15-19","20+"))))

 

it works because excel will stop at the first TRUE condition. If P12 is 9 for example, you dont need the AND to check if its '>4', if it less less than 5 the first IF would have been true.

or use Sergei's answer which is even better..

Hi Sergei. This worked like a charm! Thanks so much!

Thank you Phillip. This works also. I appreciate the explanation. I have a better understanding now of how such statements work.

It is very efficient. I appreciate both answers!

You are welcome