0

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.