Hi everyone,
I am a beginner with excel programming, so if there is a better way do what I am attempting please tell me. I am using nested if statements to determine where a value is in a range of different values. Everything is working perfectly when a value is entered into C2, however, when C2 is left blank and no value is entered, it is returning a value of 20. I cannot understand why. I would like for it to either remain blank, or return a value of 0.
=IF(AND(D2>0.05,D2<0.0999),"1",IF(AND(D2>0.1,D2<0.1499),"2",IF(AND(D2>0.15,D2<0.1999),"5",IF(AND(D2> 0.2,D2<0.2499),"9",IF(AND(D2>0.25,D2<0.2999),"14",IF(D2>0.2999,"20",IF(D2<0,"-2","0")))))))
Austin
don't use qutes around numbers "1"
"Relax. What is mind? No matter. What is matter? Never mind!"
Works OK on my system, but try this on yours...
=IF(D2>0.2999,20,IF(D2>0.2499,9,IF(D2>0.1999,5,IF(D2>0.1499,2,IF(D2>0.0999,1,0)))))
dangelor, your solution will work.
However, if you won't use LOOKUP function as I showed, my advice is to rather use >= approach
=IF(D2>=0.3,20,IF(D2>=0.25,9,IF(D2>=0.2,5,IF(D2>=0.15,2,IF(D2>=0.1,1,0)))))
Also, you missing -2 result for negative numbers (as I see it in original example)
"Relax. What is mind? No matter. What is matter? Never mind!"
I have tried each of the above suggestions. they all return 20 if C2is left
blank... any other assistance would be appreciated.
Did you try solution I've gave you in post 2?
"Relax. What is mind? No matter. What is matter? Never mind!"
Yes i remove all quotes around the numbers. i also tried the >= solution offered. all have the same result where 20 is returned if c2 is left blank.
could someone provide a link to a lookup tutorial that i couldapply here. i understand how it works but cannot figure out how to get it to seach a range of numbers
I don0t know where you looking since it return 0. Post number 2 (not my second post)....
http://www.excelforum.com/excel-prog...=1#post2672386
"Relax. What is mind? No matter. What is matter? Never mind!"
When i take out the quotes and do not put a value into C2 (on the spreadsheet i attached) the value returned is 20. thanks for the link!
well, you've sad: I would like for it to either remain blank, or return a value of 0.
If you want blank, use one more IF statement (and this works generaly):
=IF(A1="", "", formula)
"Relax. What is mind? No matter. What is matter? Never mind!"
I already have 7 if statements. excel will not allow any more.
Could you give me an example of how to implement the lookup function for this problem..
that may ne the best route seeing as i need more if statements and they arnt allowed.
Wow im dumb...youposted a solved spreddsheet. thank you it works perfectly
This is 13th post already and in half of them I refer to 2nd post.
Please, look attachemnt I've uploaded there and try to implement solution I've posted there. If it doesn't work explain what's not working.
"Relax. What is mind? No matter. What is matter? Never mind!"
Sorry im just new to these forums and did not realise that a reply could post an atachment. its my fault. thank you so much for your help. im sorry to have wasted your time. yiur solution works perfectly!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks