Help

Monthly Amount formula in a budget with various frequencies of amounts

Topic Labels: Formulas
Solved
Jump to Solution
254 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!