Hi All,
Im trying to build a nested IF formula that can run through my columns values and allocate the right number grade.
So far it looks like this however its not returning the correct values
=IF(T5<0,"-1",IF(T5<-4,"-2",IF(T5<-8,"-3",IF(T5<=-12,"-4",IF(T5<-12,"-5",IF(T5=0,"1",IF(T5<=4,"2",IF(T5<=8,"3",IF(T5<=12,"4",IF(T5>12,"5"))))))))))
Any help would be greatly appreciated,
Thanks in advance !
You have to get the order right
and your IFs dont make sense
IF(T5 <=-12 then -4
IF(T5 < -12 then -5
So which is it IF T5 <=-12 -4 or -5 ? It cant be both
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
If(t5>12,5,Lookup(T5,{-5,-4,-8,-12,0,4,8,12,12},{-1,-2,-3,-4,-5,1,2,3,4}))
"ThankyouFor Attention * And Your Help!!"
the IF runs in order
so T5 < 0 would also pickup -4 , and any of the - numbers so you would only get 0 -1 for any - (minus) number
you then have a
T5<=-12,"-4",IF(T5<-12
so the second T5<-12 will never be true
you can still use an IF but look at the order
= you want
-5 if T5 is less than -12
-4 If T5 = -12
-3 if less than -8
-2 if less than -4
-1 if less than 0
so do them in that ORDER
=IF(T5<-12,-5,IF(T5=-12,-4,IF(T5<-8,-3,IF(T5<-4,-2,IF(T5<0,-1,IF(T5>12,5,IF(T5=0,1,IF(T5<4,2,IF(T5<8,3,IF(T5<=12,4,"Not Meet Criteria"))))))))))
Putting numbers in "" also makes them text and not a number
what happens
a lookup would probably work better
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
and remove the spaces, I dont think you need them.
Looks like it should actually be this:
=IF(T5<-12,-5,IF(T5=-12,4,IF(T5<-8,-3,IF(T5<-4,-2,IF(T5<0,-1,IF(T5=0,1,IF(T5>12,5,IF(T5>8,4,IF(T5>4=3,2)))))))))
Thanks for the prompt replies everyone. Special-K you seemed to have nailed it in regards to what I was looking for. Thanks for being so informative and keeping my formula etiquette in check :P
Really guys thanks so much for taking your time to respond to my issue, means alot
Credit should go to the others who replied as I corrected a mistake in my original reply based on their answers!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks