formula to limit expenses to 1 of 2 values

[cynomolgous]

My spreadsheet tracks expenses for faculty accounts. They are limited to
$700 for conference travel OR $500 for other research expenses.
I added a column for a code, C for conference and O for other, and used
these formulas to keep a running total of each type of expense:

=SUM(IF((F13:F90)="C",D13:E90))
=SUM(IF((F13:F90)="O",D13:E90))

Now I want to have a formula in cell G8 to show me the remaining funds,
whether the funds have been spent on travel or research. In other words, if
columns D and E (encumbered and actual expenses) total $500 in expeses coded
"O," or total $700 in expenses coded "C," G8 should show zero.
Is this possible? Thank you!

 

[cynomolgous]

Good point. "Something left" would be $700 minus conference expenses or $500
minus other expenses.

 

[Bernie Deitrick]

cynomolgous:

=IF(SUM(IF(F13:F90="C",D13:E90))>=700,0,IF(SUM(IF(F13:F90="O",D13:E90))>=500,0,500-SUM(IF(F13:F90="O",D13:E90))))
=IF(SUM(IF(F13:F90="C",D13:E90))>=700,0,IF(SUM(IF(F13:F90="O",D13:E90))>=500,0,700-SUM(IF(F13:F90="C",D13:E90))))

OR to combine them

=IF(SUM(IF(F13:F90="C",D13:E90))>=700,0,IF(SUM(IF(F13:F90="O",D13:E90))>=500,0,"O: " &
500-SUM(IF(F13:F90="O",D13:E90)) & " C:- " & 700-SUM(IF(F13:F90="C",D13:E90))))


Note that you should take out any line breaks introduced by either my news reader or your web
interface.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Better yet:

="O: " & MAX(0, 500-SUM(IF(F13:F90="O",D13:E90)))& " C: " & MAX(0,
700-SUM(IF(F13:F90="C",D13:E90)))

Or in two different cells

="O: " & MAX(0, 500-SUM(IF(F13:F90="O",D13:E90)))
="C: " & MAX(0, 700-SUM(IF(F13:F90="C",D13:E90)))

HTH,
Bernie
MS Excel MVP