Comments on: Use the new Excel IFS function instead of nested IF

From this short tutorial you'll learn about the new IFS function and see how it simplifies writing nested IF in Excel. You'll also find its syntax and a couple of use cases with examples... Continue reading →

Comments page 2. Total comments: 71

  1. Hi, I need Excel function for Calculating a certain % of Discount basis number of days from a date:
    If Cut off Date is Greater than the Month indicated then 0%; If cut off date is Lower than the month indicated then return Value as per discount.
    Cut off Date 1Apr 1May 1Jun 1Jul 1Aug 1Sep 1Oct 1Nov 1Dec 1Jan 1Feb 1Mar
    A - 1st Feb 2020
    B - 15th March 2020
    C - 1st September 2020
    D - 15th October 2020

    1. Hello!
      If I understand your task correctly, use something like this

      =IFS(A1>DATE(2021,3,1),1,A1>DATE(2021,1,1),2,A1>DATE(2020,12,1),3,A1>DATE(2020,11,1),4,A1>DATE(2020,10,1),5)

      In the first condition, use the farthest date.

  2. Salab Calls form-To Incentive per calls
    Slab1 1841-2084 2.50₹
    Slab2 2025-2116 3.50₹
    Slab3 2117-2207 4.50₹
    Slab4 2208-2300 5.50₹
    Slab5 2301-Above 10₹ how to calculate in total rupees by Excel formula.

  3. Every 2 weeks a payment is made and entered, the beginning amount of payment are decreased by 1 every second Thursday. During the off weeks an extra payment can be made and is entered in a separate Col. Col 3. When the extra payment is made i would like the reflection of that payment shown in Col 2 by subtracting from the number of payments remaining and not effecting the bi-weekly payment / remaining payments.

    Payments remaining = 110

    Col. 1 has a payment of $200.00
    Col. 2 has 109 (number of payments remaining)
    with an IF statement,

    -if(col. 1>0,payments remaining-1,"")

    Note that each payment made the IF statement changes from Payments remaining-1 to Col. 2 (previous cell number) -1 so it would be

    -if(col. 1>0,Previous cell-1,"") Previous cell being 109

    In addition to this there is another Col, Col. 3 for EXTRA payments where, when entered on a row with no other payments entered, i would like THAT payment reflected in the payments remaining total.

    If you want the spreadsheet let me know .... can email it to you

    Thanks
    Cat

  4. Hi everybody...
    I have problem related, I think, to using IF function or IFS.
    I have a Pivot filter including 3 options, means I can select 1 or All or Multiple product categories. I am using SUMIFS to extract the data from Excel table, and filter using Pivot filter. all working fine until I select 2 product categories, I eliminate the problem when selecting (ALL), but could not do that when I select 2 product categories, the result was all zero values. here is my function:

    =SUMIFS(SalesData[revenue];SalesData[year];I$4;SalesData[region];$H5;SalesData[Category];IF($I$2="(All)";"*";$I$2))

    my attempt was nested IF:
    IF($I$2="(All)";"*";IF($I$2="(Multiple Items)";$I$2))
    but it dose not work, any help...

  5. with nested IF(), latter redundant calculations are ignored (not calculated)

    =IF(1=1,"Answer","this bit could be processor heavy but is ignored")

    but using the 'evaluate formula' it would appear that IFS() works out all the bits of the formula, even if the first condition is met and therefore is redundant

    =IFS(1=1,"answer", other condition IS verified, redundant terms all calculated)

    Is this the case?

    essentially, IFS does indeed look simpler to untangle, BUT is it actually getting excel to do a less efficient amount of calculating?

    1. Mike you are correct. It appears as though IFS is horribly inefficient compared to traditional nested IFS statements. Much easier to write but you end up sitting around longer waiting on calculations to finish.

    2. Hello, Mike,

      We haven’t investigated the efficiency of the IF() and IFS() functions in all configurations.
      Most likely, they work in the same way. For example, if you create two formulas like the ones below:
      =IF(1=1,"1",IF("=1","2"))
      =IFS(1=1,"1", "=1", "2")
      They return the same result equal to «1».
      But following your hypothesis, the =IFS(1=1,"1", "=1", "2") formula should have returned a #VALUE! error as the second condition in the formulas is wrong.

  6. hi
    why in my excel(2010 version) "IFS" function does not work??
    what should I do??

    1. Google pwrIFS as an option.

  7. =IF(AND(C3:BA3=0), "DISTRICT LEADER",IF(AND(C3>=15, D3>=5),"DIVISONAL LEADER",IF(AND(C3>=15, D3>=5,E3>=5,F3>=3,G3>=15,H3>=75,C3>=15,D3>5, I3>=45),"REGIONAL LEADER",IF(AND(C3>=15,D3>=5,E3>=5,F3>=3,G3>=15,H3>=75,I3>=45,K3>=3,L3>=3,M3>=5,N3>=15,O3>=45,P3>=45,Q3>=75),"SENIOR REGIONAL LEADER ",IF(OR(C3>=15,D3>=5,E3>=5,F3>=3,G3>=15,H3>=75,I3>=45,K3>=3,L3>=3,M3>=5,N3>=15,O3>=45,P3>=45,Q3>=75,S3>=4,T3>=3,U3>=3,V3>=5,W3>=15,X3>=60,Y3>=45,Z3>=45,AA3>=75), "REGIONAL MANAGER", IF(AND(C3>=15,D3>=5,E3>=5,F3>=3,G3>=15,H3>=75,I3>=45,K3>=3,L3>=3,M3>=5,N3>=15,O3>=45,P3>=45,Q3>=75,S3>=4,T3>=3,U3>=3,V3>=5,W3>=15,X3>=60,Y3>=45,Z3>=45,AA3>=75,AC3>=5,AD3>=4,AE3>=3,AF3>=3,AG3>=5,AH3>=15,AI3>=75,AJ3>=60,AK3=45,AL3>=45,AM3>=75), "SNR. REG. MANAGER", IF(AND(C3>=15,D3>=5,E3>=5,F3>=3,G3>=15,H3>=75,I3>=45,K3>=3,L3>=3,M3>=5,N3>=15,O3>=45,P3>=45,Q3>=75,S3>=4,T3>=3,U3>=3,V3>=5,W3>=15,X3>=60,Y3>=45,Z3>=45,AA3>=75,AC3>=5,AD3>=4,AE3>=3,AF3>=3,AG3>=5,AH3>=15,AI3>=75,AJ3>=60,AK3=45,AL3>=45,AM3>=75,AO3>=6,AP3>=4,AQ3>=3,AR3>=3,AS3>=5,AT3>=15,AU3>=90,AV3>=75,AW3>=60,AX3>=45,AY3>=45,AZ3>=45,BA3>=75),"RVP", "SEN. REG. MANAGER")))))))

  8. Hi Faye,

    You need to add the additional column in your table and use the following formula to increase the price by 5%:
    =D1*1.05
    Then please copy the added column and use the Paste Special - Values option to replace the values in Column D.

  9. I need excel function to calculate and update old selling price of same product number located in different rows in same column
    example:

    Product#1001-099
    Old Price = $20.00
    New Price should be increased by 5%

    Product is in Column A
    Selling Price is Column D

    How do I increase and replace the old Selling Price
    for this product that is in Column A,
    Row 8, Row 101, Row228?

    Thank you for your assistance

    1. self-reply: same issue affects all three URL in the "You may also be interested" section.

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 :)