i am looking for a way to have an if statement that has at most 20 possible inputs and display only 3 outputs so "if (A1=a or b display ans "40") if (A1=c or d or e or f display and "45") if (A1=g or h or i ...or t display and "50")
all the letters "a" through "t" represent a word
for example: If i need to know the value of "h" it will display "50"
Try this
=IF(OR(A1="a",A1="b"),40,IF(OR(A1="c",A1="d",A1="e",A1="f"),45,50))
This assumes there isn't a 21st option ie "u"
Happy with my advice? Click on the * reputation button below
Make a tabel with the inputoptions and in the next column add the display-values.
After that you can use VLookup to find the results.
Advantice:
1) The formula keeps shorter and easier to understand.
2) It is easier to change the values.
3) depends on the excel version, you don't get problems with the "limited" if statements.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Thank You Crooza your information works perfectly!!!!
You can shorten that a bit:Originally Posted by Crooza
=IF(OR(A1={"a","b"}),40,IF(OR(A1={"c","d","e","f"}),45,50))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks