 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008Formula
Hi from Aussie I have the following formula however only the first two requirements show up - anything under 12 continues to show the "Warning" sentence - What am I doing wrong please? =IF(M2=0,0,IF(M2>=16,"STOP You have reached EXTREME",IF(M2<16>12,"WARNING High Rating",IF(M2<12>10,"MEDIUM Rating",IF(M2<8>10,"LOW Rating",SUM(M2)))))) I'm guessing this is what you had in mind: =IF(M2=0,0,IF(M2>=16,"STOP You have reached EXTREME",IF(M2>=12,"WARNING High Rating",IF(M2>=10,"MEDIUM Rating",IF(M2>=8,"LOW Rating",M2)))))  SignatureBiff Microsoft Excel MVP > Hi from Aussie > [quoted text clipped - 6 lines] > High Rating",IF(M2<12>10,"MEDIUM Rating",IF(M2<8>10,"LOW > Rating",SUM(M2)))))) P.S. >IF(M2<8>10 I'm not sure how to interpret that so I guessed. SignatureBiff Microsoft Excel MVP > I'm guessing this is what you had in mind: > [quoted text clipped - 11 lines] >> High Rating",IF(M2<12>10,"MEDIUM Rating",IF(M2<8>10,"LOW >> Rating",SUM(M2)))))) >P.S. > [quoted text clipped - 7 lines] >>> High Rating",IF(M2<12>10,"MEDIUM Rating",IF(M2<8>10,"LOW >>> Rating",SUM(M2)))))) Try this formula =LOOKUP(M2,{0,10,12,16;"LOW RATING","MEDIUM RATING","HIGH RATING","You have reached EXTREME"}) It will should give the desired result and is easier to read/change edvwvw >>P.S. >> [quoted text clipped - 17 lines] > > edvwvw >>=IF(M2=0,0,....IF(M2<8>10,"LOW Rating",SUM(M2)))))) My interpretation is: If M2 = 0 then 0 If M2 >0 and <8 then M2 You're missing that test and a cell reference can't be used in an array constant. The easiest way to do this is to create a 2 column table: ..........A..........B.......... 1........0..........=M2 2........8.........LOW Rating 3.......10........MEDIUM Rating 4.......12........WARNING High Rating 5.......16........STOP You have reached EXTREME Then: =IF(M2="","",VLOOKUP(M2,A1:B5,2) SignatureBiff Microsoft Excel MVP Many thanks to you both - Very green when it comes to LOOKUP so didn't venture down that path - Re-did the formula based on the infor Biff sent through - works a treat I see I was using too many calculations ie < & >. Once again the discussion group save the day. :-) You're welcome. Thanks for the feedback! SignatureBiff Microsoft Excel MVP > Many thanks to you both - Very green when it comes to LOOKUP so didn't > venture down that path - Re-did the formula based on the infor Biff sent > through - works a treat I see I was using too many calculations ie < & >. > Once again the discussion group save the day. :-) Many thanks to you both - still very green when it comes to the LOOKUP stuff but the way Biff wrote it works a treat ...Phew! so pleased. Once again the Discussion group save the day :-). |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.