Google-sheets – Problem with Nested IF() statement on google sheets

formulasgoogle sheets

I am trying to create a formula for a monthly expense report using the following nested IF() statement: 

=IF(B2="Weekdays", C2*20,
 IF(B2="Monthly", C2, 
 IF(B2="Weekly", C2*4, 
 IF(B2="Bi-weekly", C2*8, 
 IF(B2="Bi-monthly", C2*2, "N/A")
))))

Where B column represents the recurrence of the expense and C column represents the value of the expense.

I used "N/A" as the value_if_false to check that the formula was properly generating the monthly expense for each item.

I am getting "N/A" back for a few of my expenses, but I don't understand why, since the rows which return "N/A" have recurrences which were calculated elsewhere in the column. I have triple-checked spelling.

If anyone can identify a problem with my formula or an alternate, more concise formula, it would be greatly appreciated.

Best Answer

You can try =switch() for this type of need then expand the formula :

=SWITCH(B2,
            "Weekdays"   ,C2*20,
            "Monthly"    ,C2,
            "Weekly"     ,C2*4,
            "Bi-weekly"  ,C2*8,
            "Bi-monthly" ,C2*2
       )

With arrayformula :

=ArrayFormula(SWITCH(B2:B10,"Weekdays",C2:C10*20,"Monthly",C2:C10,"Weekly",C2:C10*4,"Bi-weekly",C2:C10*8,"Bi-monthly",C2:C10*2))

Proof :

enter image description here

=Switch()

Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met.
Source : https://support.google.com/docs/answer/7013690?hl=en

I didn't add the optional default value because the default value is N/A. Formula is not caps-sensitive as you can see in B7 and B8

Related Topic