IF Formula for Budgeting

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)

image

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?

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.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.