Forum Discussion
Need help with an IFS formula
I have static ranges in each cell that are listed below and I need to add weights of 1 through 7 based on range in cell. I tried the following formula and it failed as well as trying 2-3 others with the same result.
=IFS(C3=0-250m,"1"),[C3=250M-500M,"2"],[C3=501M-1B,"3"],[C3=1.1B-2B,"4"],[C3=2.1B-3B,"5"],[C3=3.1B-4B,"6"],[C3=4.1B+,"7"])
0-250M
250M-500M
500M-1B
1.1B-2B
2.1B-3B
3.1B-4B
4.1+
Thank you for any help that may be provided!!
Mike
- Thank you for your response. This resulted in placing a "1" for cells with 0-250M but #N/A for all other cells
8 Replies
- Thank you to all for your responses. I was able to solve the issue by adjusting the range verbiage from using a "-" to "to". This removed the thought of an equation and simplified to a text lookup.
That could be like
=lookup(c3, {0, 250e6, 500e6, 1.1e9, 2.1e9, 3.1e9, 4.1e9}, {1,2,3,4,5,6,7} )- Though correct, I would suggest not to hard-code values in formulas SergeiBaklan
For actual data I also do not recommend to hardcode. At the same time without knowing of data and Excel version it's hard to recommend to use new table, or use existing range, or use named array constant.
Just a pattern.
measter First hunch: use
=IFS(C3="0-250m",1),C3="250M-500M",2,C3="501M-1B",3,C3="1.1B-2B",4,C3="2.1B-3B",5,C3="3.1B-4B",6,C3="4.1B+",7)
But if you want this to work when C3 contains 125M (which falls in the 0-250M category) then you need a different approach, see attached.JKPieterse thank you for your response. This method also throws the when the firt character is = or - excel thinks it is a formula. I have tried placement of the ' in many places without success so I am unsure as to how to get around it thinking it is an equation. On a positive note the ranges are static, of only those listed, so once the IFS statement works there will not be any variables to create an error.
=MATCH(C3, {"0-250M","250M-500M","500M-1B","1.1B-2B","2.1B-3B","3.1B-4B","4B+"}, 0)
- Thank you for your response. This resulted in placing a "1" for cells with 0-250M but #N/A for all other cells
