Income/Expense Formula Help

[hsimonds6]

I need help creating a formula that basically says:

IF E12="Income" then the number typed in f12 will be positive
If e12="expense" then the number typed in f12 will be negative

Just trying to eliminate some work as I'm a profit and loss analyst and type thousands of numbers a day...

Example below:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Transaction Type[/TD]
[TD]Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Income[/TD]
[TD]$55.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Expense[/TD]
[TD]-$100[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks in Advance:

Haley

 

[Joe4]

Welcome to the Board!

A cell can either contain:
- a hard coded-value
- a formula
But never both at the same time. So I think your question is going to require VBA.
Right-click on the sheet tab name at the bottom of the screen, select View Code, and enter this code in the resuling VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   See if updated data is in column F
    Set rng = Intersect(Target, Columns("F:F"))
    
    If Not rng Is Nothing Then
        Application.EnableEvents = False
        For Each cell In rng
            Select Case UCase(cell.Offset(0, -1))
                Case "INCOME"
                    cell.Value = Abs(cell)
                Case "EXPENSE"
                    cell.Value = Abs(cell) * -1
            End Select
        Next cell
        Application.EnableEvents = True
    End If
          
End Sub

 

[Eric W]

Depending on your sheet layout, and what you plan to do with those numbers, there's another option. You could put a Conditional Formatting rule in column F that checks to see if the cell in column E says "expense", and if it does, then highlight the F cell red. Then whatever formulas you have that reference column F, you'd have to change to look at column E too. For example, if you had this formula to sum column F:

=SUM(F:F)

you could change it to:

=SUMIF(E:E,"income",F:F)-SUMIF(E:E,"expense",F:F)