| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| is there a formula for quarterly expenses in Excel? | Excel Worksheet Functions | |||
| How to limit sum function to numeric values | Excel Discussion (Misc queries) | |||
| How to Limit Cell Values to Multiple of 10? | Excel Worksheet Functions | |||
| how to sum highest ranking values meeting criteria within a limit? | Excel Discussion (Misc queries) | |||
| limit choice in cell to one of two values; | Excel Discussion (Misc queries) | |||
Hybrid Mode
