formula to limit expenses to 1 of 2 values
 

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!

formula to limit expenses to 1 of 2 values
 

Try

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


--
__________________________________
HTH

Bob

"cynomolgous" wrote in message
...
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!

formula to limit expenses to 1 of 2 values
 

cynomolgous,

In G8, array enter (enter using Ctrl-Shift Enter)

=IF(OR(SUM(IF(F13:F90="C",D13:E90))=700,SUM(IF(F1 3:F90="O",D13:E90))=500),0,"Something left")

Not sure what you wanted to show otherwise....

HTH,
Bernie
MS Excel MVP


"cynomolgous" wrote in message
...
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!

formula to limit expenses to 1 of 2 values
 

I get the #VALUE! error.

"Bob Phillips" wrote:

Try

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


--
__________________________________
HTH

Bob

"cynomolgous" wrote in message
...
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!

formula to limit expenses to 1 of 2 values
 

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

"Bernie Deitrick" wrote:

cynomolgous,

In G8, array enter (enter using Ctrl-Shift Enter)

=IF(OR(SUM(IF(F13:F90="C",D13:E90))=700,SUM(IF(F1 3:F90="O",D13:E90))=500),0,"Something left")

Not sure what you wanted to show otherwise....

HTH,
Bernie
MS Excel MVP


"cynomolgous" wrote in message
...
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!

formula to limit expenses to 1 of 2 values
 

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


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

"Bernie Deitrick" wrote:

cynomolgous,

In G8, array enter (enter using Ctrl-Shift Enter)

=IF(OR(SUM(IF(F13:F90="C",D13:E90))=700,SUM(IF(F1 3:F90="O",D13:E90))=500),0,"Something left")

Not sure what you wanted to show otherwise....

HTH,
Bernie
MS Excel MVP


"cynomolgous" wrote in message
...
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!

formula to limit expenses to 1 of 2 values
 

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


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

"Bernie Deitrick" wrote:

cynomolgous,

In G8, array enter (enter using Ctrl-Shift Enter)

=IF(OR(SUM(IF(F13:F90="C",D13:E90))=700,SUM(IF(F1 3:F90="O",D13:E90))=500),0,"Something left")

Not sure what you wanted to show otherwise....

HTH,
Bernie
MS Excel MVP


"cynomolgous" wrote in message
...
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!