|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 19th, 2009, 06:23 PM
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
June 19th, 2009, 06:44 PM
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
June 19th, 2009, 06:51 PM
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!
|
|
#4
June 19th, 2009, 06:59 PM
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!
|
|
#5
June 19th, 2009, 07:16 PM
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
June 19th, 2009, 07:31 PM
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
June 19th, 2009, 07:34 PM
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!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
