Formula show #ERROR! when Substitute from a Number...

[MC_Carter]

Thanks in advance for your help here! I have created a formula field that has nested IF statements that multiplies a Currency field by either a 12, or a 4, depending if the payment is monthly or quarterly. The result displayed is numerically correct, but when I try to format the number shown, the Formatting tab in the Customize Field Type dialog displays, “Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.” The greater issue for me here, beyond the formatting, is not being able to sum or do other calculations on the field.

IF({Payment frequency}=“Monthly”,{Price AUD}*12,IF({Payment frequency}=“Quarterly”,{Price AUD}*4,IF({Payment frequency}=“Annually”,{Price AUD},"")))

It doesn’t make sense to me that the calculation works fine, but the returned result is, it seems, considered to be text, not a number?

Thanks in advance for your insights everyone!

Cheers
MC

[David_Skinner]

It might be because you have “” as the ELSE result in your formula. Try putting BLANK() there instead.

IF({Payment frequency}=“Monthly”,{Price AUD}*12,IF({Payment frequency}=“Quarterly”,{Price AUD}*4,IF({Payment frequency}=“Annually”,{Price AUD},BLANK())))

[MC_Carter]

Thank you SO much @David_Skinner! You nailed the issue straight up. All working now! Thanks again!

[Justin_Barrett]

FWIW, you can also just leave out BLANK() completely. If the final (ELSE) element in an IF function is omitted, Airtable automatically defaults to the appropriate equivalent of BLANK() based on the type of data generated by the rest of the formula.

[MC_Carter]

Thanks @Justin_Barrett — a very handy tip to know!