This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Monthly Amount formula in a budget with various fr...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
254
4

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 26, 2024 04:41 PM

I am creating a budget and have amounts incoming at various frequencies. I want to create a field that calculates the monthly amount based on a formula. This is the formula I have got but I get an Invalid Formula error message:

IF(

{Frequency} = "Daily", {Amount} * 30,

IF(

{Frequency} = "Weekly", {Amount} * 4.33,

IF(

{Frequency} = "Bi-weekly", {Amount} * 2.167,

IF(

{Frequency} = "Monthly", {Amount},

IF(

{Frequency} = "Quarterly", {Amount} / 3,

IF(

{Frequency} = "Twice-Annually", {Amount} / 6,

IF(

{Frequency} = "Annually", {Amount} / 12,

0 // Default case, if none of the above matches

)

)

)

)

)

)

)

How do I need to correct it?

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 28, 2024 08:31 PM

Looks like you deleted the last "0" at the end of your formula; it should be "/12, 0"?

```
IF(
{Frequency} = "Daily",
{Amount} * 30,
IF(
{Frequency} = "Weekly",
{Amount} * 4.33,
IF(
{Frequency} = "Bi-weekly",
{Amount} * 2.167,
IF(
{Frequency} = "Monthly",
{Amount},
IF(
{Frequency} = "Quarterly",
{Amount} / 3,
IF(
{Frequency} = "Twice-Annually",
{Amount} / 6,
IF(
{Frequency} = "Annually",
{Amount} / 12,
0
)
)
)
)
)
)
)
```

Reply

4 Replies 4

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 26, 2024 06:34 PM

There's no commenting system for formulas I'm afraid. Once you remove the // it'll work fine:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 28, 2024 01:36 PM

Thank you! I now have it like this but it's still giving me an the error "Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.":

IF(

{Frequency} = "Daily", {Amount} * 30,

IF(

{Frequency} = "Weekly", {Amount} * 4.33,

IF(

{Frequency} = "Bi-weekly", {Amount} * 2.167,

IF(

{Frequency} = "Monthly", {Amount},

IF(

{Frequency} = "Quarterly", {Amount} / 3,

IF(

{Frequency} = "Twice-Annually", {Amount} / 6,

IF(

{Frequency} = "Annually", {Amount} / 12,

)

)

)

)

)

)

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 28, 2024 08:31 PM

Looks like you deleted the last "0" at the end of your formula; it should be "/12, 0"?

```
IF(
{Frequency} = "Daily",
{Amount} * 30,
IF(
{Frequency} = "Weekly",
{Amount} * 4.33,
IF(
{Frequency} = "Bi-weekly",
{Amount} * 2.167,
IF(
{Frequency} = "Monthly",
{Amount},
IF(
{Frequency} = "Quarterly",
{Amount} / 3,
IF(
{Frequency} = "Twice-Annually",
{Amount} / 6,
IF(
{Frequency} = "Annually",
{Amount} / 12,
0
)
)
)
)
)
)
)
```

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 29, 2024 11:18 AM

Yes, that worked! Thank you so much!