Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Monthly Amount formula in a budget with various frequencies of amounts

Topic Labels: Formulas
Solved
Jump to Solution
919 4
cancel
Showing results for 
Search instead for 
Did you mean: 
CWP_PC
4 - Data Explorer
4 - Data Explorer

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?

1 Solution

Accepted Solutions

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 
                        )
                    )
                )
            )
        )
    )
)

 

See Solution in Thread

4 Replies 4

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

Screenshot 2024-06-27 at 9.33.46 AM.png

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,
)
)
)
)
)
)

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 
                        )
                    )
                )
            )
        )
    )
)

 

Yes, that worked! Thank you so much!