Excel Nested IF statement: examples, best practices and alternatives

The tutorial explains how to use the nested IF function in Excel to check multiple conditions. You will also learn a few other functions that could be good alternatives to using a nested formula in Excel.

How do you usually implement a decision-making logic in your Excel worksheets? In most cases, you'd use an IF formula to test your condition and return one value if the condition is met, another value if the condition is not met. To evaluate more than one condition and return different values depending on the results, you nest multiple IFs inside each other.

Though very popular, the nested IF statement is not the only way to check multiple conditions in Excel. In this tutorial, you will find a handful of alternatives that are definitely worth exploring.

Excel nested IF statement

Here's the classic Excel nested IF formula in a generic form:

IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4)))

You can see that each subsequent IF function is embedded into the value_if_false argument of the previous function. Each IF function is enclosed in its own set of parentheses, but all the closing parentheses are at the end of the formula.

Our generic nested IF formula evaluates 3 conditions, and returns 4 different results (result 4 is returned if none of the conditions is TRUE). Translated into a human language, this nested IF statement tells Excel to do the following:

Test condition1, if TRUE - return result1, if FALSE -
test condition2, if TRUE - return result2, if FALSE -
test condition3, if TRUE - return result3, if FALSE -
return result4

As an example, let's find out commissions for a number of sellers based on the amount of sales they've made:

Commission Sales
3% $1 - $50
5% $51 - $100
7% $101 - $150
10% Over $150

In math, changing the order of addends does not change the sum. In Excel, changing the order of IF functions changes the result. Why? Because a nested IF formula returns a value corresponding to the first TRUE condition. Therefore, in your nested IF statements, it's very important to arrange the conditions in the right direction - high to low or low to high, depending on your formula's logic. In our case, we check the "highest" condition first, then the "second highest", and so on:

=IF(B2>150, 10%, IF(B2>=101, 7%, IF(B2>=51, 5%, IF(B2>=1, 3%, ""))))

Excel nested IF statement

If we placed the conditions in the reverse order, from the bottom up, the results would be all wrong because our formula would stop after the first logical test (B2>=1) for any value greater than 1. Let's say, we have $100 in sales - it is greater than 1, so the formula would not check other conditions and return 3% as the result.

If you'd rather arrange the conditions from low to high, then use the "less than" operator and evaluate the "lowest" condition first, then the "second lowest", and so on:

=IF($B2<1, 0%, IF($B2<51, 3%, IF($B2<101, 5%, IF($B2<=150, 7%, 10%))))

As you see, it takes quite a lot of thought to build the logic of a nested IF statement correctly all the way to the end. And although Microsoft Excel allows nesting up to 64 IF functions in one formula, it is not something you'd really want to do in your worksheets. So, if you (or someone else) are gazing at your Excel nested IF formula trying to figure out what it actually does, it's time to reconsider your strategy and probably choose another tool in your arsenal.

For more information, please see Excel nested IF statement.

Nested IF with OR/AND conditions

In case you need to evaluate a few sets of different conditions, you can express those conditions using OR as well as AND function, nest the functions inside IF statements, and then nest the IF statements into each other.

Nested IF in Excel with OR statements

By using the OR function you can check two or more different conditions in the logical test of each IF function and return TRUE if any (at least one) of the OR arguments evaluates to TRUE. To see how it actually works, please consider the following example.

Supposing, you have two columns of sales, say January sales in column B and February sales in column C. You wish to check the numbers in both columns and calculate the commission based on a higher number. In other words, you build a formula with the following logic: if either Jan or Feb sales are greater than $150, the seller gets 10% commission, if Jan or Feb sales are greater than or equal to $101, the seller gets 7% commission, and so on.

To have it done, write a few OF statements like OR(B2>150, C2>150) and nest them into the logical tests of the IF functions discussed above. As the result, you get this formula:

=IF(OR(B2>150, C2>150), 10%, IF(OR(B2>=101, C2>=101),7%, IF(OR(B2>=51, C2>=51), 5%, IF(OR(B2>=1, C2>=1), 3%, ""))))

And have the commission assigned based on the higher sales amount:
Nested IF with multiple OR conditions

For more formula examples, please see Excel IF OR statement.

Nested IF in Excel with AND statements

If your logical tests include multiple conditions, and all of those conditions should evaluate to TRUE, express them by using the AND function.

For example, to assign the commissions based on a lower number of sales, take the above formula and replace OR with AND statements. To put it differently, you tell Excel to return 10% only if Jan and Feb sales are greater than $150, 7% if Jan and Feb sales are greater than or equal to $101, and so on.

=IF(AND(B2>150, C2>150), 10%, IF(AND(B2>=101, C2>=101), 7%, IF(AND(B2>=51, C2>=51), 5%, IF(AND(B2>=1, C2>=1), 3%, ""))))

As the result, our nested IF formula calculates the commission based on the lower number in columns B and C. If either column is empty, there is no commission at all because none of the AND conditions is met:
Nested IF with AND statements

If you'd like to return 0% instead of blank cells, replace an empty string (''") in the last argument with 0%:

=IF(AND(B2>150, C2>150), 10%, IF(AND(B2>=101, C2>=101), 7%, IF(AND(B2>=51, C2>=51), 5%, IF(AND(B2>=1, C2>=1), 3%, 0%))))

Nested IF with multiple AND conditions

More information can be found here: Excel IF with multiple AND/OR conditions.

VLOOKUP instead of nested IF in Excel

When you are dealing with "scales", i.e. continuous ranges of numerical values that together cover the entire range, in most cases you can use the VLOOKUP function instead of nested IFs.

For starters, make a reference table like shown in the screenshot below. And then, build a Vlookup formula with approximate match, i.e. with the range_lookup argument set to TRUE.

Assuming the lookup value is in B2 and the reference table is F2:G5, the formula goes as follows:


Please notice that we fix the table_array with absolute references ($F$2:$G$5) for the formula to copy correctly to other cells:

VLOOKUP instead of nested IF in Excel

By setting the last argument of your Vlookup formula to TRUE, you tell Excel to search for the closest match - if an exact match is not found, return the next largest value that is smaller than the lookup value. As the result, your formula will match not only the exact values in the lookup table, but also any values that fall in between.

For example, the lookup value in B3 is $95. This number does not exist in the lookup table, and Vlookup with exact match would return an #N/A error in this case. But Vlookup with approximate match continues searching until it finds the nearest value that is less than the lookup value (which is $50 in our example) and returns a value from the second column in the same row (which is 5%).

But what if the lookup value is less than the smallest number in the lookup table or the lookup cell is empty? In this case, a Vlookup formula will return the #N/A error. If it's not what you actually want, nest VLOOKUP inside IFERROR and supply the value to output when the lookup value is not found. For example:

=IFERROR(VLOOKUP(B2, $F$2:$G$5, 2, TRUE), "Outside range")

Important note! For a Vlookup formula with approximate match to work correctly, the first column in the lookup table must be sorted in ascending order, from smallest to largest.

For more information, please see Exact match VLOOKUP vs. approximate match VLOOKUP.

IFS statement as alternative to nested IF function

In Excel 2016 and later versions, Microsoft introduced a special function to evaluate multiple conditions - the IFS function.

An IFS formula can handle up to 127 logical_test/value_if_true pairs, and the first logical test that evaluates to TRUE "wins":

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2]...)

In accordance with the above syntax, our nested IF formula can be reconstructed in this way:

=IFS(B2>150, 10%, B2>=101, 7%, B2>=51, 5%, B2>0, 3%)

Please pay attention that the IFS function returns the #N/A error if none of the specified conditions is met. To avoid this, you can add one more logical_test/value_if_true to the end of your formula that will return 0 or empty string ("") or whatever value you want if none of the previous logical tests is TRUE:

=IFS(B2>150, 10%, B2>=101, 7%, B2>=51, 5%, B2>0, 3%, TRUE, "")

As the result, our formula will return an empty string (blank cell) instead of the #N/A error if a corresponding cell in column B is empty or contains text or negative number.
Excel IFS statement to handle multiple conditions

Note. Like nested IF, Excel's IFS function returns a value corresponding to the first condition that evaluates to TRUE, which is why the order of logical tests in an IFS formula matters.

For more information, please see Excel IFS function instead of nested IF.

CHOOSE instead of nested IF formula in Excel

Another way to test multiple conditions within a single formula in Excel is using the CHOOSE function, which is designed to return a value from the list based on a position of that value.

Applied to our sample dataset, the formula takes the following shape:

=CHOOSE((B2>=1) + (B2>=51) + (B2>=101) + (B2>150), 3%, 5%, 7%, 10%)

In the first argument (index_num), you evaluate all the conditions and add up the results. Given that TRUE equates to 1 and FALSE to 0, this way you calculate the position of the value to return.

For example, the value in B2 is $150. For this value, the first 3 conditions are TRUE and the last one (B2>150) is FALSE. So, index_num equals to 3, meaning the 3rd value is returned, which is 7%.

Using CHOOSE instead of nested IF formula in Excel

Tip. If none of the logical tests is TRUE, index_num is equal to 0, and the formula returns the #VALUE! error. An easy fix is wrapping CHOOSE in the IFERROR function like this:
=IFERROR(CHOOSE((B2>=1) + (B2>=51) + (B2>=101) + (B2>150), 3%, 5%, 7%, 10%), "")

For more information, please see Excel CHOOSE function with formula examples.

SWITCH function as a concise form of nested IF in Excel

In situations when you are dealing with a fixed set of predefined values, not scales, the SWITCH function can be a compact alternative to complex nested IF statements:

SWITCH(expression, value1, result1, value2, result2, …, [default])

The SWITCH function evaluates expression against a list of values and returns the result corresponding to the first found match.

In case, you'd like to calculate the commission based on the following grades, rather than sales amounts, you could use this compact version of nested IF formula in Excel:

=SWITCH(C2, "A", 10%, "B", 7%, "C", 5%, "D", 3%, "")

Or, you can make a reference table like shown in the screenshot below and use cell references instead of hardcoded values:

=SWITCH(C2, $F$2, $G$2, $F$3, $G$3, $F$4, $G$4, $F$5, $G$5, "")

Please notice that we lock all references except the first one with the $ sign to prevent them from changing when copying the formula to other cells:

SWITCH function - a compact form of a nested IF formula in Excel

Note. The SWITCH function is only available in Excel 2016 and higher.

For more information, please see SWITCH function - the compact form of nested IF statement.

Concatenating multiple IF functions in Excel

As mentioned in the previous example, the SWITCH function was introduced only in Excel 2016. To handle similar tasks in older Excel versions, you can combine two or more IF statements by using the Concatenate operator (&) or the CONCATENATE function.

For example:

=(IF(C2="a", 10%, "") & IF(C2="b", 7%, "") & IF(C2="c", 5%, "") & IF(C2="d", 3%, ""))*1


=CONCATENATE(IF(C2="a", 10%, ""), IF(C2="b", 7%, ""), IF(C2="c", 5%, "") & IF(C2="d", 3%, ""))*1

Concatenating multiple IF functions in Excel

As you may have noticed, we multiply the result by 1 in both formulas. It is done to convert a string returned by the Concatenate formula to a number. If your expected output is text, then the multiplication operation is not needed.

For more information, please see CONCATENATE function in Excel.

You can see that Microsoft Excel provides a handful of good alternatives to nested IF formulas, and hopefully this tutorial has given you some clues on how to leverage them in your worksheets. To have a closer look at the examples discussed in this tutorial, you are welcome to download our sample workbook below. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel nested If statement - examples (.xlsx file)

You may also be interested in

159 comments to "Excel Nested IF statement: examples, best practices and alternatives"

  1. Arfan Kafait says:

    Hi, sir thank you for the information you give us about excel. I have a question.

    Develop only one formula (drag & drop) starting from cell C8 until cell C13.
    The conditions are as the followings
    1) if the last cell for each row is greater or equal to the value of the previous cell (the result is Yes)
    2) if the last cell for each row is less than the value of the previous cell; then, (the result is No)

    In row 1, cell K1 has the value 3, cell J1 has 1
    In row 2, cell J2 has the value 2, cell I2 has 6
    In row 3, cell I3 has the value 8, cell H3 has 8
    In row 4, cell H4 has the value 1, cell G4 has 2
    In row 5, cell F5 has the value 2, cell E5 has 3
    In row 2, cell I6 has the value 8, cell H6 has 6

    The above mentioned is a table. The values are in multiple columns. I need the answer in a single column.


  2. Kaido says:

    Ablebits Team, thank you for the information you have on here, so wonderful. I'm looking for a formula which would match a person to a scheduled job at a particular time. The conditions for a person to be assigned to the job are; the person has to have a particular rating in order to do the job, the named person should be available for the job for the time slot (not blocked) and one person can do multiple jobs so long as he has the correct rating and he's available. This is a sports official kind of job assignment. I'm basically trying to match about fifty officials to about one hundred games (and all of the officials should get at least one game) and I wondered if excel would be able to help me by cutting down on the amount of time.

    Thank you and I look forward to your response.

  3. Sulaiman Hassan Alao says:

    Hello. I am working to find a formula like these 12 subjects e.g (English, Mathematics, Civic education, computer........)
    I want to pick English >=50% & Mathematic >=50% and other six subjects>=50% for promotion
    How can I do sir/ma.
    Thank you.

  4. Helena says:


    You have been so helpful with one of my previous questions. Can you tell me why this formula isn't working please? =if(OR(H2="GSD",H2="GR x L"), K2+N3, K2+N4). In the column H there are lots of variations of words, I'd like the formula to check for 3 variations of these words e.g. GSD or GR x L and if it finds them to then add time in N3 to a date held in K2. If it doesn't find that combination of words I really just want it to just show the same information in K2 without adding anything on (but have added a zero in n4 so it does something. Hope that makes sense! Thank you

      • Helena says:

        Hi Alexander, What I want to happen is....If the cell H2 contains 3 possible selection of words eg 'GSD', 'GSD xL' that the cell with the formula in looks at a date in K2 and and adds on time which is held in N3. If it doesn't find those words, it doesn't add time to the date and just shows the same date as already shown in K2. I've tried to account for the 'if not' part by adding on 0 which is held in N4 but not sure I need that. Hope that makes more sense.

          • Helena says:

            Thanks Alexander, the formula doesn't seem to be working. as nothing changes. Could it be because I'm trying to look for 3 different sets of words? So it would be 'GSD' or 'GSD x L' not all of them?

              • Helena says:

                Source data
                in column H it could read "GSD", or "GSD x L", or " L x GSD", or "GR"
                in column K there are a load of dates e.g. 16/06/21, or 05/03/21
                in cell N3 there is the number 429 and in N4 there is the number 0
                I would like a formula that looks at the text in column H and if it finds the text "GSD", "GSD xL", or "L X GSD" then it adds time on (N3 = 429) to the date held in column K and displays this as a new date. If it doesn't find that text it adds on nothing (n4 - 0) to the date - so just replicates the date in already in column K.
                Sorry, I'm probably not being very clear. I really appreciate your time!

  5. Sue Mackie says:

    Hi again,

    Disregard my message above, I worked it out:


    Thanks for the information above, I read it properly and it worked.

  6. Sue Mackie says:

    Hi there :)

    I am having problems using the IF statement not sure if I should be using the AND or the OR function in it, or possibly the IFS would help.

    Perhaps you could help me?

    I work for a sporting club and what I'm trying to do is have an outcome of "Premiers/Runners Up, Third or Fourth, based on the position of the column prior - SEMI's Outcome - (which shows either "DNQ" - Did not qualify, "Play for Third" or "Into Grand Final".

    K Column is Ladder Position at end of Season
    L Column is No. of teams in Grade
    M Column is DNQ or FINALS (based on <4 being in Finals)
    N Column is Home team Score in Semi Final
    O Column is Away team score in Semi Final
    T Column is whether it means we Won or Lost
    Q Column is Semi's outcome (either DNQ, Play for Third, or Into GF)
    R Column is Home team Score in Grand Final
    S Column is Away team score in Grand Final
    T Column is whether it means we Won or Lost
    U Column shows either "Premiers", Runners Up, Third or Fourth.

    Basically, I need to have four outcomes of a possible equation for the end result in Column U.
    If our team wins the grand final and we were "INTO GF" in Column Q, then Premiers, otherwise "Runners Up. If Q showed Play for Third, and our team won, then it should show THIRD, otherwise FOURTH.
    I'd also like if our team didn't qualify at all for the Grand Final that it would show DNQ.

    I hope that make sense ;)

    Thank you for your help


  7. Joanna Jones says:

    I want to apply all of the follow scenarios in one statement, but no matter what I try I am clearly missing something!

    I have 3 fields that matter: Product Code(E3), Order Date(K3) and Ship Date(L3)

    Test 1: If the product code (E3) = "HV" AND the Ship Date (L3) is > Order Date (K3) + 2, I want to use the Ship Date (L3), otherwise I want to use Order Date (K3) + 2 workdays

    Test 2: If the product code(E3) = "AF" AND the Ship Date (L3) is > Order Date(K3) + 5, I want to use the Ship Date (L3), otherwise I want to use Order Date (K3) + 5 workdays

    Test 3: if it is any other product code (E3) AND the Ship Date (L3) is > Order Date (K3) +1, I want to use this Ship Date (L3), otherwise I want to use the Order Date (K3) + 1 workday

    Any help would be appreciated!

      • Joanna J says:

        I appreciate your quick feedback! While your answer didn't actually perform what I wanted it to, it actually gave me the idea to adjust my thinking and I was able to make it work afterall. I changed to a "less than" approach from a "more than" approach and BINGO!! I think I have my answer! Thanks again!

  8. ken says:

    Thanks for the info,

    However, in your first illustration, what do we do if, for instance; in the sales column B5 and B8 are blank cells. And we want to return the commisionas unknown

  9. Nicola says:

    I would really appreciate assistance with this formula.

    I have a cell A51 that can contain one of three words "receipt", "contract", "invoice"

    If cell A51 says receipt I need cell L51/0,8775
    If cell A51 says contract I need cell L51*38%
    If cell A51 says invoice I need cell L51*0,19

    This is the formula I have tried (amongst numerous attempts)

  10. Rehmatullah Butt says:

    Respected Sir,
    I have learned from your above blog regarding nested if function in excel. In your first example the formula shown in screenshot doesn't meet the criteria condition mentioned in blog. Instead the formula should be like "=IF($B2>=151, 10%, IF($B2>=101, 7%, IF($B2>51, 5%, IF($B2>=1, 3%, 0%))))". Secondly, I realized it doesn't make any difference in result whether you chose High to Low in your formula or Low to High. Excel never make any error in calculation or your required results. Only required condition is that you needed to write your formula correctly. I did for you like this "=IF($B2<1, 0%, IF($B2<51, 3%, IF($B2<101, 5%, IF($B2<151, 7%, 10%))))". English is not my first language so I might made some errors in text. If anything written by me hurts the feelings of anybody I do apologize with all.
    Sincere regards,

    • Hello Rehmatullah,

      Thank you for your feedback.

      You are right about the first formula. To meet the stated conditions precisely, the first logical test should be $B2>=151 or $B2>150.

      As for the order of conditions, we are actually talking about the same thing but in different words :) Whichever direction you choose, it is important to place the nested functions in the right order. In case of "high to low" arrangement, you use the "greater than" operator and check the highest condition first, then the second highest, and so on. In case of "low to high", you use the "less than" operator and start with testing the lowest condition, then the second lowest, etc.

  11. john shervell says:

    hi the first ie increases by .1 works ,but in the second if it does not go down by .2 per g2-36 (g2 is 40)and i also need to decrease by .3 .4 .5 per differance.

    ps number of brackets at the end may be incorrect as i shortened the formula for this question.


  12. Robert Hayes says:


    I am looking to perform a nested IF which looks a the value of two cells (column v and column an) and returns values as follows:

    If cell V2 ="Pass" show the text "Proposed" else "Requires Exam" then as part of the same if statement:
    If cell AN2 =text "Licenced" else the result of the earlier V2 result

    I am sure I am missing something obvious as so far I have tried the following:

    =IF(V2="Pass","Proposed","Requires Exam"(IF(ISTEXT(AN2),"Licenced","Result of V2="Pass","Proposed","Requires Exam"

  13. kyle says:

    i am trying to add to the if(and function to display information from other cells after it is through as shown below
    column info
    A-their first name
    B-their last name
    D their sex "M" or "F"
    E- client or staff

    =IF(E3="staff", "Dr.", IF(AND(E3="client", D3="M"), "Mr.", IF(AND(E3="client", D3="F"), "Ms.",)))

    I am trying to get the above formula to display (Dr./Mr./Ms.) (First Name from column A) (Last Name from column B) in one cell "Dr. Gregory House"

  14. Olympia says:

    I am attempting and if and or statement, but having trouble getting to the correct result. part I
    If(AJ11="US","TRUE",IF(AJ11="",IF(AG11="US","TRUE",IF(AG11="",IF(AI11="US","TRUE","review"))))), returning false instead of True or review.
    there are 3 col with data points, if no US or blank = true, if any us, blank and non us, test

    if result is to test, the 2 part is to test for 2 letter code to either test or not test.


  15. Antoinette says:

    Hi there,

    I am struggling with my formula I'd love some help:
    I have 8 data values validated in cell I2, and will be entering a manual number in cell J2 and believe I need an IF formula in column K to easily calculate the following scenario.

    If the frequency = weekly (cell I2), and the number of hours =x, then the monthly hours when annualised would be (J2*52)/12).

    The other values in I2 are:
    2 x Weekly (needs annualising then multiplying by 2)
    3 x Weekly (needs annualising then multiplying by 3)
    4 x Weekly (needs annualising then multiplying by 4)
    5 x Weekly (needs annualising then multiplying by 5)
    6 x Weekly (needs annualising then multiplying by 6)
    7 x Weekly (needs annualising then multiplying by 7)
    One-Off (does not need annualising)

    The current formula I have is:

    =IF(I2="Weekly",((J2*52)/12),IF(I2="Fortnightly",((J2*26)/12),IF(I2="3 x Weekly",((J2*52)/12)*3),IF(I2="2 x Weekly",((J2*52)/12)*2""""))))

    It worked until I put the last IF combination in

    I'd really appreciate some help. Thank-you :-)

  16. Vanessa says:


    Needed your expertise to correct my formula:

    Column A: Interval: 1,2,3,4,5,6,7,8,9,10
    Column B: P/MP/OP: OP,OP,OP,OP,MP,MP,MP,P,P,P
    Column C: Dispatch: 1.29,0.45,1.29,1.24,0.26,1.51,1.31,0.14,1.25,0.25
    Column D2: 1.30

    Formula: =IF((OR(A2="MP,A2="OP")),AND(IF(C2>=0.5,$D$2,C2)))

    The result must be: if A2 is MP or OP, and the value of C2 is greater than O.5, it will give me 1.30 answer, if C2 is below 0.5, it will give me the value of that cell. If A2 is P and C2 is lower or higher than 0.5, it will give me 0 answer. I only needed to capture the value of MP and OP.

    Thank you.

    • Hello!
      Your task is not completely clear to me.
      The condition "C2 is lower or higher than 0.5" does not make sense as it is any number. Action is not specified if no condition is met. There is a reference to D2 in the formula. Your description doesn't say anything about D2.

      • Vanessa says:

        Column D2: Firm Dispatch: 1.30 (any value in column C at a minimum requirement dispatch criteria of 0.5, it will still deliver(choose) 1.30 firm. If column C dispatch is lower than 0.5 dispatch criteria, it will choose the value of that cell in column C. Another criteria is for MP and OP only, if B2 is "P" = 0

        Column F2: My initial formula: =IF((OR(B2="MP",B2="OP")),AND(C2>0.5,$D$2,C2)))

        Sorry for the confusion, I am referring to "B2" and not "A2":

        The result must be: if B2 (not A2) is MP or OP, and the value of C2 is greater than O.5, it will give me 1.30 answer, if C2 is below 0.5, it will give me the value of that cell. If B2 is P, result must be 0. I only needed to capture the value of MP and OP.

        Hope this helps.

  17. Kelsey says:



    Using above formula to show 1 manager for every 5 employees - (ie. 6-10 employees requires 2 managers, 11-15 employees would require 3 managers, 16-20 employees would require 4 managers)

    Is there a more efficient/condensed way to accomplish this?

  18. SUBIR DAS GUPTA says:

    "IF(R10>1,Q10*5,IF(R10<0,Q10*7))" Is tje formula is right or contains any error, because i am getting only Q*5 result where it should be Q*7,

  19. Monica says:

    Need help to have text & number sequencing based on a drop down list value (to confirm document type and number)

    Column C lists document type from a 3-choice drop down list "Policy","Standard", or "Other"
    Columns E: G contain sequential values for each of the document types.
    Column E "Policy" list of sequential values 0001-PL (continues as 0002-PL, etc. starting in row 2)
    Column F "Standard" list of sequential values 0001-ST (continues as 0002-ST, etc. starting in row 2)
    Column G "Other" list of sequential values 0001-OT (continues as 0002-OT, etc. starting in row 2)

    Since Column C would have a list that is not sequential, how can I ensure column D sequences document number correctly?
    User enters in Column C Row 2 "Policy", Column D will populate as "0001-PL"
    User enters Column C Row 3 "Standard", Column D populates as "0001-ST"
    User enters Column C Row 4 "Policy", Column D populates as "0002-PL"

    How can I make sure that when someone enters one of the three document types, column D picks up last sequence of "xxx-PL"?

  20. Pink says:

    I have doubt
    Please help to formula below:
    If A1 "-", B1 "-", answer "ok"
    If A1 "", B1 "", answer "ok"
    If A1 "", B1 "-", answer "check"

  21. Ally says:

    So I am working on jasperactive, one of the projects has me come up with a function that will display Two different texts and if it doesn't match either, it will display a blank
    -the original function is: IF(AND(G2>F2,F2>E2),"Growing energy source","")
    -Then it asks for you to add in a function that will display "Shrinking energy source" is G2<F2 and F2<E2
    -i have been trying to make a nested formula for it that will work for hours with no luck. if anyone can help that would be amazing

  22. Isaac David Larnach says:

    =IF(G17>1.63,12CFW, IF(G17>1.3,10CFW,IF(G17>0.978,8CFW,IF(G17>0,6CFW,""))))

    excel says there is a problem with this formula? help would be much appreciated, thank you.

  23. nachaporn says:

    demurrage charges (free time = 3 days) 1-4/1/2021) free time
    demurrage charges = 18 days
    4-6 days = usd25
    7-9 days = usd40
    10-12days = usd60
    Thereafter = usd75

    what is if?

  24. Shelly Clark says:

    If cell A1 =family and cell A2=1 A3 =A7
    if cell A1 =family and cell A2=2 A3 =A8
    if cell A1 =family and cell A2=3 A3 =A9
    if cell A1 =Single and cell A2=1 A3 =B7
    if cell A1 =Single and cell A2=2 A3 =B8
    if cell A1 =Single and cell A2=3 A3 =B9

  25. Alice says:

    Could someone help me to figure out one If formula that involve multiple conditions.

    For example, I have 7 different project code name in G2, and G3 is associated project name.
    and when I clicked project code in G2, and G3 will auto pop out the right project name.
    I know how to compare within two code. but not sure how to do with multiple different ones.
    Below is an example that i made it up for comparing two projects.


    Thank you so much

  26. Ramesh says:

    Hello- I am trying put a formula together for the below conditions

    For Example
    IF C2=7.5 Then it should RANK 5 similarly IF C2=8.8 Then it should RANK 4

    Below Scale for Each RANK
    >11 = RANK 1
    <10 - 9- 8-<=9 = RANK 4
    <8 = RANK 5

    Please help.

  27. Richard says:

    Hi, I'm hoping that I'm not too far off the mark in this area :-).

    I have a register of risks where I want to flag as overdue. A critical must be attended to within 7 days, High 14, Med 30 and Low 60. My stab at it as below fails with an error. Any ideas please?

    X and F contain the age and severity data.


  28. Azwie says:

    I have three options...3 or less yes ...less committed, 4 yes...average commited. More than 4 yes... hifhly committed for E5 to K5...please help

  29. Prabh says:

    i am looking fro a formula for below problem.

    We have 3 cells a1,b1&c1.
    in cells d1-->if out of 3 cells only single cell contain value >0 then it will show Ok otherwise not ok.

    FOr EX:
    a b c d
    0 0 0 OK
    1 0 0 OK
    1 1 0 not ok
    0 1 0 ok

  30. murali says:


    the above formula not run please give the solution to me

  31. JASHDEEPSINH says:

    i want to do below type formula in column K
    column I is numeric 23.45

    and i want to do is if column L will -23.45 so "SL", if column L will subtract with 2 is equal to 23.45 then "1:2" and last if column L will subtract with 3 is equal to 23.45 then "1:3"
    i get only "SL" , "1:1" so
    can you help to resolve?

  32. Mitzie Leatherman says:

    I have 4 budget/accounting columns
    F4 = Budget Transfers (can be +or-)
    G4 = Purchase Order Amount Allowed
    H4 = amount paid OR "closed"
    I3 = Beginning balance (which could be zero)
    Please assist:)

    • Hello!
      I’m sorry but your task is not entirely clear to me.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.

  33. Charlie says:

    I have actually sorted this formula out now so no help needed. The formula I ended up with was


  34. Charlie says:

    I am trying to put a formula together and cant seem to get the nesting correct. What i am trying to do is get a concatenate value if the IF and AND are true based on another cell.
    My data:
    cell A1= Qtr 1
    cell J10 = Jan
    cell K10 = Feb
    cell L10 = Mar
    cell M10 = April
    cell N10= May
    cell L10= June
    cell F10 = project name
    cell G10 = project number
    What I am after is a formula that will look at cell A1 and if its equal to Qtr 1, and if cells J10(Jan), K10 (Feb) and L10 (Mar) are blank, then i want it to return blank, otherwise concatenate the project name and project number. But if A1= Qtr 2, and if M10, N10 and L10 are blank then return blank,otherwise concatenate project name and project number. And so on for quarters 3 and 4. Is this possible or is there a simpler way to do this?

    Appreciate your assistance.

    • Hello Charlie!
      If I understand your task correctly, the following formula should work for you

      =IF(AND(A1="Qtr 1",J10="",K10="",L10=""),"", IF(AND(A1="Qtr 2",M10="",N10="",O10=""),"", IF(AND(A1="Qtr 3",P10="",Q10="",R10=""),"", IF(AND(A1="Qtr 4",S10="",T10="",U10=""),"",F10&G10 ) ) ) )

      I hope this will help

  35. Denise Cline says:

    Hello. I am working to find a formula in a column that will have 1 of 4 outcomes; N/A, Not Started, Active, & Expired.
    Column A - # of Warranty Years
    Column B - Start Date of Warranty
    Column C - End Date of Warranty
    Column D - Status (N/A, Not Started, Active, or Expired)
    There are times when column A is 0, so that status is N/A.
    There are times when column A has a value, but column B & C are empty, so that status is Not Started.
    I am trying to use the TODAY() function as well so that whenever the sheet is opened it is current for expired warranties.
    Thank you for your help.

      • Denise Cline says:

        Here are some of the formulas I have tried, none of them work, and most only account for 3 of the 4 outcomes.
        =IF(AND(A1=0,"N/A","Not Started"),IF(C1>TODAY(),"Active",IF(C1TODAY(),"Active"),IF($C10,$D1=""),"Not Started"),IF($C1>TODAY(),"Active"))

  36. leks says:

    Hello Alexander Trifuntov
    I was hoping you could help with my excel function have being having trouble with.
    Am trying to archive below multiple query.
    =IF(AND (A4 = "WEEK 1"(OR( D9=({"E", "EOC1", "EOC2", "L"})) & ( D10=({"E", "EOC1", "EOC2", "L"})), "WORKING", "NOT WORKING"))).
    I can also shortened it by saying----
    =IF(AND (A4 = "WEEK 1"(OR( D9=({"E*","L"})) & ( D10=({"E*","L"})), "WORKING", "NOT WORKING"))) using a wildcard for the E's.
    so if both cells contain the range values is TRUE else FALSE.
    The formula work for single cell like this
    =IF(OR(D9=({"E";"L";"EOC1";"EOC2"})),"WORKING","NOT-WORKING" ) but am having trouble with 2 cells and concatenating it.

    Thanks in advance

    • Hello Leks!
      If I understand your task correctly, the following formula should work for you:

      =IF(AND(A4="WEEK 1", OR(D9={"E","EOC1","EOC2","L"}, D10={"E","EOC1","EOC2","L"})), "WORKING","NOT WORKING")

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      • Leks says:

        Hi Alex
        That work perfectly for me. Thank you so much, i really appreciate your quick response.

        Many Thanks

  37. Leks says:

    Hello Bharath
    I was hoping you could help with my excel function have being having trouble with.
    Am trying to archive below multiple query.
    =IF(AND (A4 = "WEEK 1"(OR( D9=({"E", "EOC1", "EOC2", "L"})) & ( D10=({"E", "EOC1", "EOC2", "L"})), "WORKING", "NOT WORKING"))).
    I can also shortened it by saying----
    =IF(AND (A4 = "WEEK 1"(OR( D9=({"E*","L"})) & ( D10=({"E*","L"})), "WORKING", "NOT WORKING"))) using a wildcard for the E's.
    so if both cells contain the range values is TRUE else FALSE.
    The formula work for single cell like this
    =IF(OR(D9=({"E";"L";"EOC1";"EOC2"})),"WORKING","NOT-WORKING" ) but am having trouble with 2 cells and concatenating it.

    Thanks in advance

  38. Ray says:

    E4 is either blank or contains a date
    I'm trying to get A4 to: 1) to be blank if E4 is blank or 2) place an X in A4 if e4=<today().
    Everything I have tried fails to produce the desired results. I have tried choose functions, if and nested ifs but nothing I'm doing is working.
    Thanks for any help.

  39. Bharath says:

    I need a formula if A1 is greater the or equal to 15 December 2019 or lesser then 14 March 2020 and if A2 says listed then say Yes if not say no or if A1 is greater then or equal to 15 March 2020 then say Yes.

  40. Swapnil says:

    1)If= first date of period all floor commission 2%
    2)If=second date of period
    lower floor commission 3% ,
    middle floor commission 3.5%
    Higher floor commission 4%
    3)If = third date of period
    Lower floor commission 2%
    Middle floor commission 2.5%
    Higher floor commission 3%

    *(Date of periods
    (1) 29-09-2019 to 02-02-2020
    (2) 03-02-2020 to 29-02-2020
    (3) 01-03-2020 to 30-03-2021)*

    Can you please help any formula to get correct commission %

  41. Abubakar Sadiq says:

    Please help me!!!

    I want to use an IF Command, but I want it in such a way that it fetches another IF Statement from a totally different Cell...

    How do I do it?

  42. Nested IF with multiple AND says:


    I would ask for your advice in choosing the easiest procedure for determining ratings testing physical ability to take data from two different cells that are related to gender (male or female) and age group (is different for men and women, and can be easily determined on the basis of age using nested IF functions). Therefore, it is necessary to include data relating to gender and age category in the test results to obtain a score which is also categorized as laid down norms. Pre grateful!

  43. Chris says:

    My apologies... it sent before I finished my question.
    Over Amt Short Amt Retailer ID
    351.20 25862
    10.00 37586
    10.00 67952
    351.20 25862
    I would like to have the formula highlight amounts in red that are the same between Over Amt (col C) and short Amt (col D), but ONLY if the Retailer IDs (col E) are the same. As you can see here, the 351.20 amounts have the same retailer ID, but the 10.00 amounts do not. Any help you can give would be appreciated.
    Thank you!

  44. ZAINAB says:


  45. Cley says:

    I have a table of information. In the D column I have multiple products with various units of measure (g, mm, ml, kg, etc) I am attempting to build a formula that searches for the specific unit of measure and populates a new column with only that unit. Eg:

    Column B3 information : DEN BRAVEN ACRYLIC BEECH/ OAK 280ml
    Information I want the formula to find and place into column D "ml"
    I have tried building a multiple IF but I it only identifies the first range of data successfully. The moment the Formula gets to a different unit of measure then it returns a #VALUE! issue.

    Column B4 Data that the formula has issues with : ALCOLIN WOODFILLER OREGAN PINE 200g

    Here is a copy of the IF:

    Could anyone give me some pointers?

  46. Lokesh says:

    A1 value is Male or Female
    B2 Value is 10000 or 5000 or 15000 or 30000
    in C2 result want if male is greater than 10000 then 200 or greater than 7500 then 175 or 0
    or C2 result want if Female is greater than 10000 then 200 or 0

  47. Maria says:

    SOS, I am totally lost here.
    I have 5 colons I need to take in my formula, with a total of 4 conditions and I need to calculate the following:
    IF and and Then
    condition1 D1460 G14=0 P14=0 =D14*$L$10
    condition3 D14+G140 G14=<60 P14=Y D14*$L$10+$M$9
    - and in all other cases it should be D14+G14
    How do I get them all in one field and make excel calculate the result with all those parameters? Is it possible at all?
    I tried with: IF((AND(D1460, G14=0), D14*$L$10, IF(D14+G14<=59, $M$9, IF((AND(G14=<60, P14=J), D14*$L$10+$M$9, D14+G14))))
    But the formula is obviously wrong :-(
    Since I am a linguist and the last time I had maths was in 1983, you can understand my confusion...
    Many thanks!

  48. Umashankar says:

    I have to update the three different states professional tax values (PT) in column B based on the Column A (Salary) & Column C (states). state wise & Salary wise PT slab given below. I tried with if condition. It is throwing me an error. Can you help
    Col A Col B Col C
    Salary PT State TN PT Slab
    1800 KL 12501 208.00
    5500 KL
    6000 KL KA PT Slab
    6500 TN 0-15000 0
    7500 TN >150001 200
    7600 KL
    8600 TN KL PT Slab
    9000 TN 20834 208
    25000 KA

  49. Tony Leukering says:

    I have a table with values, no text. The data look something like this
    0.00 0.51 1.01 0.00 1.43
    0.82 2.48 5.40 1.96 7.75
    0.39 0.00 0.00 0.93 0.00

    I need to present these values in four categories:
    0 [presenting as 0]
    >0 and <0.05 [presenting as =0.05 and =1 [presenting as the actual value]

    I have not found a way for IFS to test against a range of values within a single unit of the function, as the old AND function used to enable in IF statements. Is this possible?

    • Tony Leukering says:

      A chunk of the four categories got erased. Here are the categories, again:
      0 [presenting as 0]
      >0 and <0.05 [presenting as 0.04999 and 0.99999 [presenting as the actual value]

  50. Donna says:

    I can’t figure out how to write this...the cell that I’m evaluating is a %
    The formula I’ve tried: IF(B17≥65,"THRIVING",IF(B17≥50,"Ahead Of The Curve",IF(B17>35,"TURBULANT","Making Ends Meet")))
    Criteria: 66+% = Thriving,
    51-65% = Ahead Of The Curve,
    36-50% = Making Ends Meet,
    0-35% = Turbulent
    One cell I’m evaluating has a value of 45%, another of 88%...the formula says both are Making Ends Meet
    Can you help me?

    • ARMAN says:

      HI Donna,
      I thing this will you.
      IF(A11>=66%,"THRIVING","")&IF(A11<=65%,"Ahead Of The Curve","")&IF(A11<=50%,"Making Ends Meet","")&IF(A11<=35%,"TURBULENT","")

  51. dinesh says:

    169 #N/A
    169 #N/A
    169 #N/A
    169 Punjab National Bank
    169 #N/A
    169 #N/A

    i want to have punjab national bank in all the other places where it is #N/A. please share logic to write in other column

  52. Shruti Amburle says:

    I need a formula for excel -
    If (Salary<=13000) Then 9617 Else 12022 Elseif(Salary<=24000) Then 15100 ElseiF(salary<=30000) Then (Basic*0.45)

    Help me

    • tree says:

      Sub Value()
      Dim Salary As Integer
      Dim Basic As Double
      Basic = Cells(?, "?")
      Salary = Cells(?, "?")
      If Salary < 13001 Then
      Cells(?, "?") = 9617
      ElseIf Salary < 24001 Then
      Cells(?, "?") = 15100
      ElseIf Salary < 30001 Then
      Cells(?, "?") = Basic * 0.45
      Cells(?, "?") = 12022
      End If
      End Sub

  53. Nate says:

    I am trying to find a max value from a list which belongs to another range of data. For example, when I have a data: A=1, B=2, C=3, D=4, E=5. If the list contains A, C, D, the output should be 4 (the maximum value).
    I made a formula that works (F column: A,B,C,D,E; G column: 1,2,3,4,5; J column: list)as below: =MAX(IF(F22:F29=J21,G22:G29),IF(F22:F29=J22,G22:G29),IF(F22:F29=J23,G22:G29),IF(F22:F29=J24,G22:G29))
    But I wonder, if there is any way to make the formula simpler.

  54. Nitin Agarwal says:

    Please help. My Formula mentioned below is working correctly.
    =IF(I4>=9000, 2%, IF(I4>7500,1.75%, IF(I4>6000, "1.5%", IF(I4>4000, "1%", IF(I4=9000, 2%, IF(I4>7500,1.75%, IF(I4>6000, "1.5%", IF(I4>4000, "1%", IF(I4<4000, "0")))))), "0")

  55. Julie says:

    Thank you - this was extraordinarily helpful! The IF & IF & IF was exactly what I needed to make my column work properly. I used it in Google Sheets and it worked like a charm!

  56. Stu says:

    Afternoon, looking for some help with trending some date with date ranges. Have a data set with a lot of fluid data from multiple people and downloads. I need to 'Snap shot' the data in specific cells to track progress quickly for a trending report week on week. Complete a large search on the net but it I can not find anything the help with my problem. My current formula looks back at last weeks results which is misleading the data. Currently I have to manually over write the formula result each week manually so the data is retained and not re calculated, or over written. Any help appreciated..

  57. john says:

    hi, i have 3 different if formulas that works when entered separately. However, when i tried to combine them, the result shows "FALSE".

    =IF(E3="PREVIEW 1",IF(N3>=11,30,IF(AND(N3>=10,N3=9,N3=12,30,IF(AND(N3>=11,N3=10,N3=14,30,IF(AND(N3>=13,N3<14),24,IF(N3=13,18,""))))))))))))

  58. george kairu says:

    Please help me get the formulae for the below Ms excel problem. I need the card rate to appear automatically on the 4th column when the amount of Fixed Deposit (in millions), period of fixing (in days) & interest offered (%) is given. The card rates are given below.

    FD(Mns) Days Int Offered(%) Card Rate (%)
    2.65 31 7.50
    11.34 91 7.50
    64.21 181 8.00
    178.58 365 8.75

    Days 10Mn50 Million
    30 to 90 6.00 6.50 7.00
    90 to 180 6.50 7.00 7.25
    181 to 364 7.00 7.25 7.75
    365 to 730 7.25 7.75 8.25

  59. Guy says:

    I run a badminton booking spreadsheet with 7 named players (as column headings in row 2) and I want to identify the first 4 people who have said "YES" (in row 3), working from the left. I have nested IFs, but I want to stop trying after I have achieved the 4th YES (because you can only get 4 player on a badminton court!).
    =CONCATENATE("This week it's ",
    IF(C3="YES","me, ",""),
    IF(D3="YES",$D$2,""),IF(D3="YES",", ",""),
    IF(G3="YES",$G$2,""),IF(G3="YES",", ",""),
    IF(I3="YES",$I$2,""),IF(I3="YES",", ",""),
    IF(J3="YES",$J$2,""),IF(J3="YES",", ",""),
    IF(K3="YES",$K$2,""),IF(K3="YES",", ",""),
    "This week it's me, Roger, Sanath, Agnelo, Greg, Alec"
    which is 6 names because José in column D had said "NO".
    I would like the result to read
    "This week it's me, Roger, Sanath, Agnelo".
    I think the more gramatically correct
    "This week it's me, Roger, Sanath and Agnelo"
    might be too much of a challenge!
    Any ideas please?

    • Hi Guy,
      What if all the players reply in the affirmative? Will it be possIble to pick only four of them for a game without upsetting the others? If random selection sounds good to you, I can suggest applying a formula that will bring the names of those who want to take part (Step 1) and turning the values received into a 'Custom list' to delegate Ablebits' 'Random Generator' to take an unbiased decision (Step 2). If you like the idea, this is the formula which is needed in Step 1:

      =IF($3:$3="YES", CHOOSE(1, $2:$2), "")

  60. craig says:

    FYI =IF(G39="","",IF(J39=G39,"Contact individual",IF(M39="","",IF(M39<=J39,"Returned","Contact individual")))))

  61. craig says:

    Hello please could you wizards advise on the following:
    =IF(G40="","",IF(J40=G40,"Contact individual",IF(M40="","",IF(J40>=M40,"Returned","Contact individual")))))
    I require a nest IF (if think)
    I have three dates and three different document status outcomes depending on the sequence below.
    If the issue date is is filled in only, then the status shall be "in circulation".
    if there is now a document withdrawal date in the next cell that the is greater or equal to the issue date, the value shall be "Contact individual".
    If the return date value is missing from the required cell then the status shall remain as "Contact individual", but if a date is entered into the withdrawal date cell the status then the status is to return "Returned"
    Please Help Friends

  62. Emmanuel says:

    I need to do an IF statement to get a range for:
    Volumes Greater than and equal to 2,000,000
    Volumes Less than 2,000,000 but greater than and equal to 200,000
    Volumes less than 200,000 but greater than and equal to 500
    Volumes less than and equal to 500

  63. Abhijeet Vaidya says:

    Awesome article. I switched nested ifs for Lookup - made it so much easier. Also now I can go to the lookup table and change values without having to copy paste the formula again. Thanks....

  64. Tony Louis says:

    I need help in defining the reorder level using "IF" or any other formula in excel
    Column A = Shortage = 18500
    Column B = MOQ = 5000
    Column C = Reorder level = ?
    I would like to calculate the reorder level as follows :-
    (ie. Reorder level should be = to MOQ if shortage is less than MOQ
    OR Reorder level should be 20000 if shortage is between 15001 & 20000
    ie. Reorder level should be in multiples of MOQ but > shortage
    Kindly confirm how to use "IF" formula or any other formula in excel

  65. Skip INtro says:

    Error in your explanation of the SWITCH function. Line 1, you use SWIFT. I expect you mean SWITCH. #yourewelcome

  66. Tina says:

    I have a formula I am trying to use for Overbillings and underbillings, each a separate column. If I use one column the formula looks like this:
    =IF([@[% Comp]]<30%,[@[Earned Cost]],[@[% Comp]]*[@[Contract Amount]])-[@Billed]
    but I want it to give the result of zero if the answer to this in the overbillings is over zero. What do i add?

  67. Bernadine Robnett says:

    I am attempting to create a formula with IF statements.
    Here it is:
    G6 =TODAY() which inputs today's date.
    J6 If "Yes" is selected
    L6 will input TODAY()+7
    =IF(J6="Yes",G6=TODAY()+7) the result will go into L6
    Thank you

  68. Bethany says:

    I am attempting to create a dynamic table where the value in one cell makes another equal a 3rd cell plus the data in another cell. So for example, =IF(F2=P7,G2+Q7,"-") I can get it to work with that one---but I need to nest that statement with 10+ others of the same type (=IF(f2=P8,G2+Q8) etc etc (pulling data from a table). I'm lost.

  69. Vani says:

    Not working. Please help please. I need to use 16 conditions for if statement but as you see, it is only 10 and not working. It said that "this formula uses more levels of nesting than you can use in the current file format". Anyone please.


    =IF(S10>=95,20, IF(S10=94,19, if(s10=93,18, if(s10=92,17, if(s10=91,16, if(s10=90,15, if(s10=89,14, if(s10=88,13, if(s10=87,12, if(s10=86,11))))))))))

  70. Bob says:

    Can I use nested IF to do the following :
    I have a column with three possibilities entered in the cells : ABC DEF GHI

    the next column in the contagious cell needs a number based on the above so that :
    ABC would be 123
    DEF would be 456
    GHI would be 789

  71. Abdul says:

    I have 2 work sheets, the first one named Property 1 and the 2nd one named summery.
    In the first sheet I have expenses listed by date, amount and category. the categories are Repairs,Labor,Materials,Transport,Advertising and Commission.
    I would like to sum each category monthly (Jan,Feb.....) and display the total on the Summery sheet.

  72. , says:

    I'm trying to make a formula along the lines of if D3>= 2300 multiply by J3, once 2300 is exceed the difference should be multiplied by K3. Is this possible?

  73. Rustin says:

    My formula wont work, can anyone see where I am going wrong?
    =IF('UTILITY Rates'!A2:A13,'Utility Score Card'!C1,"'UTILITY Rates'!C2")

  74. Camilo Puentes Iregui says:

    Hello. I have look different ways, but I'm running out of time to complete a project I'm working on. I have a table like this, is a changing pattern, so there is not actually a pattern, but there are some blank cells in between the text in Column A. In Column be I have tax names, which is just next. I need to be able to concatenate a formula with if or I don;t know to be able to concatenate tax names on the row with out blanks, I need to delete the blanks but have that information in every text
    A B C
    Text1 tax1 tax1,tax2
    blank tax2 ------------
    text2 tax1 tax1,tax2
    blank tax2 --------------
    text3 tax1 tax1
    text4 tax1 tax1
    text5 tax1 tax1
    text6 tax1 tax1,tax2,tax3,tax4,tax5
    blank tax2 --------
    blank tax3 --------
    blank tax4 ---------
    blank tax5 -------
    text7 tax1 tax1,tax2,tax3,tax4
    blank tax2 ------------
    blank tax3 ------------
    blank tax4 -------------
    text8 tax1 tax1
    text9 tax2 tax2
    text10 tax3 tax3,tax2
    blank Tax2 ---------
    test11 tax1 tax1

  75. DianeBaker says:

    I'm having trouble with this formula. I have three conditions and they need to all be true to return "TRUE". If any are not true, it should return "FALSE". Here's what I've tried now, which does not work.
    =IFS(C:C="CSD_SERVICES", "TRUE", (K:K=0,"TRUE", (O:O="","TRUE","FALSE")))
    I've tried probably 25 different iterations but so far none returns the correct answer.

    • Hi,
      It seems to me that any of the two formulas below can help you with your task:

      =IF(AND(C:C="CSD_SERVICES", K:K=0, O:O=""), "TRUE", "FALSE")

      =IFS(AND(C:C="CSD_SERVICES", K:K=0, O:O=""), "TRUE", TRUE, "FALSE")

      Please note that the necessity of one more 'TRUE' in the second formula is dictated by the syntax that the Excel IFS function has. Please press 'F1' if you feel like looking into it.

  76. Catarina says:

    IF sentens:

    About getting % when buying

    Ih I by <= 4 pallets I´ll get 60%
    If I by 5 but les than 9 pallets I'll get 60% and from his price I get ekstra 5 %

    J=Amount of Pallets
    L= My price (pallets times price pr. pallet)
    M= 60 %

    This works perfectly:

    My problem comes when I add this:
    10 pallets but than 15 pallets I get 10 % (so first the 60 % and than an ekstra 10 after the first results.

    Like this:

  77. Thong says:

    I have a summary of invoices (positive) and credit notes (negative).
    When I knock off against payment made it shows zero.
    I am trying to make an if statement which would show:-
    If zero - "-"
    If more than .01 - "OS" (invoice)
    If more than -.01 - "OS" (CN)

    The aim is to filter what are the unpaid invoice and CN to generate
    a payment proposal for those outstanding.


  78. Adrian says:

    I'm using a nested IF formula for conditional formatting and to evaluate if the date in a cell is equal to today =IF(I2=TODAY(),I2,IF(J2="NDA",J2,IF(J2="SDA",J2))). I want the formula to stop if the date in cell I2 is not equal today. The formula should stop at the first false argument however the formula evaluates all the arguments and returns a NDA which is the value in J2. I have evaluated the formula using Formula Auditing and I get a false value whether or not my first logical test is nested in an nested IF formula.

  79. Oten says:

    I have the following data in C7:
    S123 - using formula will result in D7 as 80123
    SA123 - using formula will result in D7 as 81123
    E123 - using formula will result in D7 as 82123
    EA123 - using formula will result in D7 as 83123
    C123 - using formula will result in D7 as 84123
    CA123 - using formula will result in D7 as 85123
    U123 - using formula will result in D7 as 87123
    UA123 - using formula will result in D7 as 86123
    I tried to nest IF statements but excel is returning an error that maximum nesting is reached.






    Dear Sir,
    Kindly confirm one coloum are value 1-15, and secound Coloum value 15-30, if kindly confirm which sort out the value's of Below & uper.
    & kindly confirm vlookup formulas fungtions.

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)