Multiple (nested) IF statements looking up a number that comes in between two numbers
How to make an Excel nested if, else, and statement - Stack Overflow
Is Nested IF Statements the correct function to use?
Nested IF, AND, and OR statements in one Excel formula - Stack Overflow
Videos
I need to make an IF statement that references a number and then looks to see if it falls between two numbers to give an output. I've been trying nesting IF/OR, but can't seem to get it to work.
Example: number is 6
Then my "iIF" is if it falls between any of these values it will show the output in " "..
0-3.5, then "0-3.5"
3.6-7, then "3.6-7"
7.1-10, then "7.1-10"
10.1-14, then 10.1-14"
14+, then "14+"
So in this case the formula in the cell should come back "3.6-7", since 6 falls in between these two numbers.
I have two cells with two conditions: yes or no, and I’m looking to return 1 of three possible values for three different scenarios:
If A1=“No”, then “3” If A1=“Yes”, AND B2=“Yes”, then “1” If A1=“Yes”, AND B2=“No”, then “2”
I keep getting an error with my formula:
=IF(A1=“No”,3,IF(AND(A1=“Yes”,B2=“Yes”,1),IF(AND(A1=“Yes”,B2=“No”,2))
Is this the correct function to achieve what I’m looking for?
Can excel accomplish this in one cell? Very novice and insight would be appreciated.
Making such a complicated formula will result in an unmaintainable mess. Getting it working will be hard, and if you ever later discover a bug, it will be very difficult to unpack your giant formula to solve it.
The programming patterns that gets you out of this is helper columns.
Create 13 columns to the right of your data, and in each, put a formula, just as you have, but make all of the elses "" -- you'll wind up with only one column with text at each row.
Now, for your 14th column, concatenate all of your 13 columns. You'll have all of your serial numbers now in the same column.
Finally, select the 13 helpers columns, right click, and hide them -- they are just helpers, and don't need to display regularly.
The formula below consolidates your logic into 3 possibilities
=IF(SUM(N(LEN(A1)={7,10,12})),A1,
IF(SUM(COUNTIF(A1,"L???????"),COUNTIF(A1,{"L","D"}&"????????"),COUNTIF(A1,"M??????????"&{"","??"})),REPLACE(A1,1,1,""),
IF(SUM(N(LEN(A1)={8,20})),RIGHT(A1,8),"ERROR")))
- where the input entered is valid
- where the input requires the 1st character to be eliminated
- where only the last 8 characters of the input are valid
which, for the sample provided, yields the correct results:
(due to the employment of array constants, this will have to be entered as an array formula)
but this answer is purely a function of your sample which, because serial numbers are usually alphanumeric in type, I don't think is realistic, e.g. the logic involves removing leading L, D or M characters, but your data indicate that these 3 same characters can occur in the reformatted data - if the reality is that such characters are never valid then the logic could be simplified by the use of a series of SUBSTITUTE() functions.
Hey fellow Redditors,
I am working on an Excel spreadsheet tracking state/country attendance for my work, and I am trying to build a formula that references a long list (~250 rows) of country names and their country code equivalent and automatically fills in the country code in column D based off of the country name typed out in column C. After some research and testing, I’ve discovered one way to do this as nesting IF statements. The formula I currently have started is: =IF(C94=H$1,I$1,IF(C94=H$2,I$2,IF(C94=H$3,I$3,IF(C94=H$4,I$4,IF(C94=H$5,I$5,IF(C94=H$6,I$6,IF(C94=H$7,I$7,…IF(C94=H$250,I$250)))))))) I’ve only typed out a few of these statements (IF(CX=H$X,I$X)), and after some concern about character limit in formulas, I am both tired and concerned that I will not be able to finish my formula this way as it stands. There has got to be a better way to do this, I just don’t have enough Excel experience to be able to figure out how I could effectively condense this formula (tried a couple things, like trying to reference the columns by H:H and I:I; didn’t work)
Your advice is much appreciated!