Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I calculate a value based on dates?
I have a worksheet that calculates YTD (year-to-date) totals from 3
categories...labor, travel, and other expenses. I want to add a row that will separate monies spent from a range of dates (i.e. 1st of the month to the last Friday of the month) in order to calculate monies spent MTD (month-to-date). I am willing to change the monthly values if need be. |
#2
|
|||
|
|||
Hi, John-
I arrived at a solution for this by mocking up some data: ~Column headers "Date" "Type" and "Amount" in A3:C3 ~Date entries from Feb to May in A4:A15 ~Line identifiers "Labor" "Travel" and "Other" in the Type column, from B4:B15 ~Dollar amounts in C4:C15 A summary section: ~A "Month to Date" column header in A17 ~The date 2/1/2005 entered in A18:A20 formatted to display as Feb 2005 ~Line identifiers "Labor" "Travel" and "Other" in the Type column, from B8:B20 I entered this formula in C18: =SUMPRODUCT(--(MONTH($A$4:$A$15)=MONTH(A18)),--($B$4:$B$15=B18),$C$4:$C$15) The SUMPRODUCT() formula used in this context allows you to specify parameters used to sum a column of data. The "--" in the formula is a double unary operator that causes Excel to evaluate a True or False value as a 1 or 0. For a given line, if the Month parameter is True and the Line Identifier is True, Excel multiplies 1 x 1 x the value in column C, and augments the sum. If one of the parameters is False, a 0 multiplier is used: since any number times 0 is 0, the sum is not augmented. |
#3
|
|||
|
|||
Will the SUMPRODUCT formula calculate information I've entered between the
range of dates? I'm looking to take an hourly total of labor (labor hours times labor rate), a total of figures I've entered manually for travel and other expenses and see if I can calculate a total based on the range of 1st of the month to last Friday of the month. I'm growing even more confused by the minute. "Dave O" wrote: Hi, John- I arrived at a solution for this by mocking up some data: ~Column headers "Date" "Type" and "Amount" in A3:C3 ~Date entries from Feb to May in A4:A15 ~Line identifiers "Labor" "Travel" and "Other" in the Type column, from B4:B15 ~Dollar amounts in C4:C15 A summary section: ~A "Month to Date" column header in A17 ~The date 2/1/2005 entered in A18:A20 formatted to display as Feb 2005 ~Line identifiers "Labor" "Travel" and "Other" in the Type column, from B8:B20 I entered this formula in C18: =SUMPRODUCT(--(MONTH($A$4:$A$15)=MONTH(A18)),--($B$4:$B$15=B18),$C$4:$C$15) The SUMPRODUCT() formula used in this context allows you to specify parameters used to sum a column of data. The "--" in the formula is a double unary operator that causes Excel to evaluate a True or False value as a 1 or 0. For a given line, if the Month parameter is True and the Line Identifier is True, Excel multiplies 1 x 1 x the value in column C, and augments the sum. If one of the parameters is False, a 0 multiplier is used: since any number times 0 is 0, the sum is not augmented. |
#4
|
|||
|
|||
Sorry for the delayed response- I didn't see your post.
SUMPRODUCT will do whatever you tell it to do- I had mocked up some data on the assumption that the LABOR entry had a date associated with it, and the SUMPRODUCT formula I created added all the entries pertaining to a particular month. Post again showing some of your actual data (or faked data using your actual layout) and your desired result. |
#5
|
|||
|
|||
I was working with your idea using SUMPRODUCT when it hit me to do it simple
by linking previous Excel spreads. Simple sum formula was commonly used as an end result. Thanks for your help. "Dave O" wrote: Sorry for the delayed response- I didn't see your post. SUMPRODUCT will do whatever you tell it to do- I had mocked up some data on the assumption that the LABOR entry had a date associated with it, and the SUMPRODUCT formula I created added all the entries pertaining to a particular month. Post again showing some of your actual data (or faked data using your actual layout) and your desired result. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I calculate a value based on dates?
Dave hope you can help, i have trades i make (forex) listed by day and may
make 2-3 trades per day on different currencies ie 14-mar -20 Gbp 14-Mar +43 CHF 14-Mar +12 Eur I have a column which summerises the daily profit/loss on each day, but i have to manually change the field range on my sum product function each time i add new trades to the spread sheet is there anyway to automatically update the spreadsheet as new entriea are made. Also i would like the equity curve graph i have based on the daily profit/loss to be updated automatically hope you can help thank you -- thank you censura "Dave O" wrote: Hi, John- I arrived at a solution for this by mocking up some data: ~Column headers "Date" "Type" and "Amount" in A3:C3 ~Date entries from Feb to May in A4:A15 ~Line identifiers "Labor" "Travel" and "Other" in the Type column, from B4:B15 ~Dollar amounts in C4:C15 A summary section: ~A "Month to Date" column header in A17 ~The date 2/1/2005 entered in A18:A20 formatted to display as Feb 2005 ~Line identifiers "Labor" "Travel" and "Other" in the Type column, from B8:B20 I entered this formula in C18: =SUMPRODUCT(--(MONTH($A$4:$A$15)=MONTH(A18)),--($B$4:$B$15=B18),$C$4:$C$15) The SUMPRODUCT() formula used in this context allows you to specify parameters used to sum a column of data. The "--" in the formula is a double unary operator that causes Excel to evaluate a True or False value as a 1 or 0. For a given line, if the Month parameter is True and the Line Identifier is True, Excel multiplies 1 x 1 x the value in column C, and augments the sum. If one of the parameters is False, a 0 multiplier is used: since any number times 0 is 0, the sum is not augmented. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I calculate Vacation Time earned based on length of emplo. | Excel Discussion (Misc queries) | |||
formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) | |||
how do I calculate the days between dates? | Excel Discussion (Misc queries) | |||
Calculating Due Dates Based on Payments | Excel Worksheet Functions | |||
calculate with dates before 1-1-1900 | Excel Worksheet Functions |