Skip to main content

IF Formula for Budgeting

  • March 14, 2022
  • 1 reply
  • 0 views
W

I am reconfiguring my budget spreadsheet and am having trouble with an IF formula.

I want to set up the base so that I can select if something is revenue or an expense, enter the dollar amount, and then in the next column over (Debits and Credits), it will automatically add a (-) negative to any item that is marked as an expense. Here is the formula I was using:

IF({Trans Type}=“Expense”,{Transaction Amount}*-1)&IF({Trans Type}=“Revenue”,{Transaction Amount}*1)

The formula is messing things up somehow because it won’t recognize the output as a number, and therefore will not let me format it as currency. Instead, it says: 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.

I want this column to be formatted as currency and show a running total. Can someone help?

1 reply

Ben_Young1
Forum|alt.badge.img+20
  • Ben_Young1
  • Brainy
  • 520 replies
  • March 15, 2022

Hey @Washington_Center!

Two things you’ll want to do here:

Firstly, in the configuration for your formula field, you’ll want to change the format of the value that the formula returns.

You can do that as shown below:

Next is the formula.
Here’s the formula that I tossed together using the field names that you provided:

IF(
    AND(
        {Trans Type},
        {Transaction Amount}
    ),
    IF(
        {Trans Type} = "Expense",
        VALUE(
            "-" & {Transaction Amount}
        ),
        {Transaction Amount}
    )
)

Re. Running Total

The field summary feature could come in handy here.
See below:

Let me know if there’s something I’m missing, or if you have any additional questions.

Reply


Most helpful members this week

Terms & Conditions