Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Something Missing
I have a problem with the following formula
=SUMIF(Sheet1!$F$3:$F$248,"<"&DATE(2007,1,1),Sheet 1!$M$3:$M$248)/SUMIF(Sheet1!$F$3:$F$248,"<"&DATE(2007,1,1),Sheet1 !$D$3:$D$248) Based on the SUMIF statement I look at a date range and if it falls within a certain time frame I take the sum of a dollar column and divide it by the total cost of another column to get a %. This works as is, but what I need this do is now is roughly the same thing except I do not want the $0 values to column M to be calculated in the overall formula. I believe the $0 value is throwing my outputted % number off. Does someone have a better way? Thanks Peter W |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Something Missing
If you had this data in column M and D
M D 1 59 2 48 0 26 4 55 what would be your desired result? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Something Missing
With M being the dollars made and D being the dollar sold a third range would
column F (Date range) I want to find all the dates within a range (F2:F1000) and sum all the dollars made within that range (M2:M1000) and divide the sum against the total dollars sold for that range. If any number in column M is 0 I don't want to add the corresponding cell in column D to the total, this throws my total percentage off because the 0 is being included in the overall performance. "Herbert Seidenberg" wrote: If you had this data in column M and D M D 1 59 2 48 0 26 4 55 what would be your desired result? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Something Missing
For this example, with the defined names as shown
DaT DoM DoS 12/28/2006 1 59 12/29/2006 2 48 12/30/2006 0 26 12/31/2006 4 55 1/1/2007 5 23 1/2/2007 6 61 =SUMPRODUCT((DaT<DATE(2007,1,1))*DoM)/ SUMPRODUCT((DaT<DATE(2007,1,1))*(DoM0)*DoS) =4.32% Your SUMIF formula would give the wrong answer of 3.72% |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Something Missing
Thanks Herbert, this worked great.
Peter "Herbert Seidenberg" wrote: For this example, with the defined names as shown DaT DoM DoS 12/28/2006 1 59 12/29/2006 2 48 12/30/2006 0 26 12/31/2006 4 55 1/1/2007 5 23 1/2/2007 6 61 =SUMPRODUCT((DaT<DATE(2007,1,1))*DoM)/ SUMPRODUCT((DaT<DATE(2007,1,1))*(DoM0)*DoS) =4.32% Your SUMIF formula would give the wrong answer of 3.72% |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What am I missing? | Excel Worksheet Functions | |||
Who is missing ? | Excel Discussion (Misc queries) | |||
add-ins missing | Excel Worksheet Functions | |||
Toolbars Missing, And option to Add Missing | Excel Discussion (Misc queries) | |||
Missing Row | Excel Worksheet Functions |