Forum Discussion

JohnF118's avatar
JohnF118
Copper Contributor
Jun 04, 2020

if and ifs

 

I have a Microsoft 365 user subscription.

I want to use the IF formuls for multiple conditions. It works well for one condition  such as:

 =IF(B14=0," ",(B14/B$8)-1)

When I need to add a few more conditions it yields a blank but does not calculate;

=IFS(B17=0," ",B$8=0," ",C$8=0," ",TRUE,(B17/B$8))

I tried the new IFS and it does yield a blank but not a result

=IFS(B18=0," ",B$8=0," ",C$8=0," ",TRUE,(B18/B$8))

I was on the phone with Microsoft support but she was not able to find a solution after much time.

case # 100 585 3754.

 

excel

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 04, 2020

    JohnF118 

    I'm sorry, but what's the difference between

    =IFS(B18=0,"",B$8=0,"",C$8=0,"",TRUE,(B18/B$8))
and
=IF(OR(B18=0,B$8=0,C$8=0),"",(B18/B$8))

    they return exactly the same result, did I miss something?

    One more could be

    =IFERROR(B18/B18*C$8/C$8*B18/B$8,"")
  • hynguyen's avatar
    hynguyen
    Iron Contributor
    Jun 04, 2020

    JohnF118 I think your syntax for IFS is not correct.

    Check this https://support.office.com/en-us/article/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45

    As you can see, the correct syntax should be =IFS([Something is True 1, Value if True 1,Something is True 2,Value if True 2,Something is True 3,Value if True 3)

    where "Something" must be the same, and the only changes are the values you want to test for it.

    You use B17, B$8, C$8 as "Something" instead and that's why your formula does not work.

    I suggest you use: = IF(or(B17=0,B$8=0,C$8=0)," ",B17/B$8)

  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    Jun 04, 2020

    Hi JohnF118 ,

     

    can you post a data sample, so we can see what's in the cells referenced in the formula? Also mock up what result you would expect.

     

    What does this formula do: =IFS(B18=0," ",B$8=0," ",C$8=0," ",TRUE,(B18/B$8))

     

    if B18 equals zero, it returns a blank,

    otherwise (i.e. if B18 is not zero), it checks the next condition, so

    if B8 equals zero, it returns a blank,

    otherwise, (i.e. if B8 is not zero either) it checks the next condition, so

    if C8 equals zero, it returns a blank, 

    otherwise, (i.e. if C8 is not zero either)  it checks the next condition, so

    the next condition is the TRUE value and the function divides B18 by B8

     

    Is that what you want to do?

     

    Maybe you want to check all three cells and if any of them are zero, then return a blank, else do the division?

     

    That means you need to combine the conditions with an OR() statement in a regular IF() function, like this

     

    =IF(or(B18=0,B$8=0,C$8=0)," ",(B18/B$8))

     

    If this is not it, what do you want to do? Can you describe the logic in words?

Resources