|
complex count question
All,
a1:a1000 contains a list of dates including duplicates and b1:b1000 contains amounts. I need to calculate the average amount by day of the month. For example, 8/10/2004 2000 9/10/2004 1000 9/10/2004 5000 The total for the 10th day is 8000 and the average for the 10th day is 8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000 so the average of 2000 and 6000 is 4000. d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain the average by day. So, in this example, e10 = 6000. The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to get the denominator. -- Jim |
Hi
apart from the fact that i'm totally confused by your example - i think the following formula will work for you: =SUMPRODUCT(--(DAY($A$1:$A$3)=D10),$B$1:$B$3)/SUMPRODUCT(--(DAY($A$1:$A$3)=10)) Cheers julieD "JBoulton" wrote in message ... All, a1:a1000 contains a list of dates including duplicates and b1:b1000 contains amounts. I need to calculate the average amount by day of the month. For example, 8/10/2004 2000 9/10/2004 1000 9/10/2004 5000 The total for the 10th day is 8000 and the average for the 10th day is 8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000 so the average of 2000 and 6000 is 4000. d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain the average by day. So, in this example, e10 = 6000. The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to get the denominator. -- Jim |
JulieD,
I'm sorry to confuse you with the example, but it's critical to the question at hand. Maybe I can try it again. The question is what is the average amount attributable to each calenday day, regardless of how many items may occur on a specific individual day? In my example, 8/10/2004 had one transaction and 9/10/2004 had two transactions. So, the total for 9/10/2004 was 6000. That averaged with the total for 8/10/2004 of 2000 is 4000. Or, on average the amount for the 10th day is 4000. Your proposed solution produces 2,666.67 because the denominator calculates to 3. I need a denominator of 2 because there are only two days=10 in the sample data. "JulieD" wrote: Hi apart from the fact that i'm totally confused by your example - i think the following formula will work for you: =SUMPRODUCT(--(DAY($A$1:$A$3)=D10),$B$1:$B$3)/SUMPRODUCT(--(DAY($A$1:$A$3)=10)) Cheers julieD "JBoulton" wrote in message ... All, a1:a1000 contains a list of dates including duplicates and b1:b1000 contains amounts. I need to calculate the average amount by day of the month. For example, 8/10/2004 2000 9/10/2004 1000 9/10/2004 5000 The total for the 10th day is 8000 and the average for the 10th day is 8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000 so the average of 2000 and 6000 is 4000. d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain the average by day. So, in this example, e10 = 6000. The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to get the denominator. -- Jim |
Copy this into D1 (watch the wrap), press ctrl + shift +
enter, and fill down: =SUM((DAY($A$1:$A$1000)=D1)*($B$1:$B$1000))/SUM(-- (FREQUENCY(IF(DAY($A$1:$A$1000)=D1,MATCH ($A$1:$A$1000,$A$1:$A$1000,0)),MATCH ($A$1:$A$1000,$A$1:$A$1000,0))0)) HTH Jason Atlanta, GA -----Original Message----- All, a1:a1000 contains a list of dates including duplicates and b1:b1000 contains amounts. I need to calculate the average amount by day of the month. For example, 8/10/2004 2000 9/10/2004 1000 9/10/2004 5000 The total for the 10th day is 8000 and the average for the 10th day is 8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000 so the average of 2000 and 6000 is 4000. d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain the average by day. So, in this example, e10 = 6000. The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to get the denominator. -- Jim . |
Try...
E1, copied down: =SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/MAX(1,SUM(IF(FREQUENCY(IF((DAY($A $1:$A$100)=D1)*($B$1:$B$100<""),$A$1:$A$100),IF(( DAY($A$1:$A$100)=D1)*($ B$1:$B$100<""),$A$1:$A$100))0,1,0))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "JBoulton" wrote: All, a1:a1000 contains a list of dates including duplicates and b1:b1000 contains amounts. I need to calculate the average amount by day of the month. For example, 8/10/2004 2000 9/10/2004 1000 9/10/2004 5000 The total for the 10th day is 8000 and the average for the 10th day is 8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000 so the average of 2000 and 6000 is 4000. d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain the average by day. So, in this example, e10 = 6000. The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to get the denominator. |
Jason,
Thanks for the suggestion. I tried to use frequency() as well, but couldn't get it right. I think you may be clsoe to the solution, but the proposed function evaluates to #N/A. I've taken it apart and found that the problem is in the denominator (surprise.) I'll work with it a little and see if I can figure it out following your suggestion. "Jason Morin" wrote: Copy this into D1 (watch the wrap), press ctrl + shift + enter, and fill down: =SUM((DAY($A$1:$A$1000)=D1)*($B$1:$B$1000))/SUM(-- (FREQUENCY(IF(DAY($A$1:$A$1000)=D1,MATCH ($A$1:$A$1000,$A$1:$A$1000,0)),MATCH ($A$1:$A$1000,$A$1:$A$1000,0))0)) HTH Jason Atlanta, GA -----Original Message----- All, a1:a1000 contains a list of dates including duplicates and b1:b1000 contains amounts. I need to calculate the average amount by day of the month. For example, 8/10/2004 2000 9/10/2004 1000 9/10/2004 5000 The total for the 10th day is 8000 and the average for the 10th day is 8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000 so the average of 2000 and 6000 is 4000. d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain the average by day. So, in this example, e10 = 6000. The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to get the denominator. -- Jim . |
The formula sums the quantity in col. B for the specific
day of the month, and then divides by the total *unique* dates that fall on the that day of the month. 1/10/05, 1/10/05, 2/10/05, 2/10/05, 3/10/05 = 3 unique dates for the 10th. It works in my test. If you want a sample workbook, email me (replace OPPOSITEOFCOLD with you know what) with the subject line: complex count question. HTH Jason Atlanta, GA -----Original Message----- Jason, Thanks for the suggestion. I tried to use frequency() as well, but couldn't get it right. I think you may be clsoe to the solution, but the proposed function evaluates to #N/A. I've taken it apart and found that the problem is in the denominator (surprise.) I'll work with it a little and see if I can figure it out following your suggestion. "Jason Morin" wrote: Copy this into D1 (watch the wrap), press ctrl + shift + enter, and fill down: =SUM((DAY($A$1:$A$1000)=D1)*($B$1:$B$1000))/SUM(-- (FREQUENCY(IF(DAY($A$1:$A$1000)=D1,MATCH ($A$1:$A$1000,$A$1:$A$1000,0)),MATCH ($A$1:$A$1000,$A$1:$A$1000,0))0)) HTH Jason Atlanta, GA -----Original Message----- All, a1:a1000 contains a list of dates including duplicates and b1:b1000 contains amounts. I need to calculate the average amount by day of the month. For example, 8/10/2004 2000 9/10/2004 1000 9/10/2004 5000 The total for the 10th day is 8000 and the average for the 10th day is 8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000 so the average of 2000 and 6000 is 4000. d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain the average by day. So, in this example, e10 = 6000. The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to get the denominator. -- Jim . . |
Jason,
Your function works perfectly. Thanks for the help. "Jason Morin" wrote: Copy this into D1 (watch the wrap), press ctrl + shift + enter, and fill down: =SUM((DAY($A$1:$A$1000)=D1)*($B$1:$B$1000))/SUM(-- (FREQUENCY(IF(DAY($A$1:$A$1000)=D1,MATCH ($A$1:$A$1000,$A$1:$A$1000,0)),MATCH ($A$1:$A$1000,$A$1:$A$1000,0))0)) HTH Jason Atlanta, GA -----Original Message----- All, a1:a1000 contains a list of dates including duplicates and b1:b1000 contains amounts. I need to calculate the average amount by day of the month. For example, 8/10/2004 2000 9/10/2004 1000 9/10/2004 5000 The total for the 10th day is 8000 and the average for the 10th day is 8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000 so the average of 2000 and 6000 is 4000. d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain the average by day. So, in this example, e10 = 6000. The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to get the denominator. -- Jim . |
Domenic,
Perfect! Thanks for the help. "Domenic" wrote: Try... E1, copied down: =SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/MAX(1,SUM(IF(FREQUENCY(IF((DAY($A $1:$A$100)=D1)*($B$1:$B$100<""),$A$1:$A$100),IF(( DAY($A$1:$A$100)=D1)*($ B$1:$B$100<""),$A$1:$A$100))0,1,0))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "JBoulton" wrote: All, a1:a1000 contains a list of dates including duplicates and b1:b1000 contains amounts. I need to calculate the average amount by day of the month. For example, 8/10/2004 2000 9/10/2004 1000 9/10/2004 5000 The total for the 10th day is 8000 and the average for the 10th day is 8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000 so the average of 2000 and 6000 is 4000. d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain the average by day. So, in this example, e10 = 6000. The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to get the denominator. |
On Wed, 23 Mar 2005 07:53:06 -0800, "JBoulton"
wrote: All, a1:a1000 contains a list of dates including duplicates and b1:b1000 contains amounts. I need to calculate the average amount by day of the month. For example, 8/10/2004 2000 9/10/2004 1000 9/10/2004 5000 The total for the 10th day is 8000 and the average for the 10th day is 8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000 so the average of 2000 and 6000 is 4000. d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain the average by day. So, in this example, e10 = 6000. The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to get the denominator. Try this array function. To enter an array function, after copy/pasting it into the cell, hold <ctrl<shift while hitting <enter XL will place braces {...} around the formula: =IF(SUM(1*(DAY($A$1:$A$100)=D1))=0,"", SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/SUM(--(FREQUENCY( IF(DAY($A$1:$A$100)=D1,MATCH($A$1:$A$100,$A$1:$A$1 00,0)), ROW(INDIRECT("1:"&ROWS($A$1:$A$100))))0))) Note that this formula will produce a null string if there are no entries for a particular date. --ron |
Ron,
That's a great solution, too. Thanks for the lesson. "Ron Rosenfeld" wrote: On Wed, 23 Mar 2005 07:53:06 -0800, "JBoulton" wrote: All, a1:a1000 contains a list of dates including duplicates and b1:b1000 contains amounts. I need to calculate the average amount by day of the month. For example, 8/10/2004 2000 9/10/2004 1000 9/10/2004 5000 The total for the 10th day is 8000 and the average for the 10th day is 8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000 so the average of 2000 and 6000 is 4000. d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain the average by day. So, in this example, e10 = 6000. The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to get the denominator. Try this array function. To enter an array function, after copy/pasting it into the cell, hold <ctrl<shift while hitting <enter XL will place braces {...} around the formula: =IF(SUM(1*(DAY($A$1:$A$100)=D1))=0,"", SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/SUM(--(FREQUENCY( IF(DAY($A$1:$A$100)=D1,MATCH($A$1:$A$100,$A$1:$A$1 00,0)), ROW(INDIRECT("1:"&ROWS($A$1:$A$100))))0))) Note that this formula will produce a null string if there are no entries for a particular date. --ron |
JBoulton,
Please note that my formula differs slightly from those provided by both Ron and Jason. Consider the following... 8/10/04 1000 9/10/04 1500 9/10/04 1250 1/5/05 1750 1/5/05 1800 3/5/05 2250 4/5/05 2500 4/5/05 1900 4/5/05 2300 10/5/05 Fifth day of the month average: My formula --- 4166.67 Other formulas --- 3125 As you can see, 10/5/05 is not taken into consideration until a number, including zero, is entered in the corresponding cell in Column B. I don't know if this makes a difference or whether this is an issue, but I thought I'd bring it to your attention. To get the same results as the other formulas... =SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/MAX(1,SUM(IF(FREQUENCY(IF(DAY($A$ 1:$A$100)=D1,$A$1:$A$100),IF(DAY($A$1:$A$100)=D1,$ A$1:$A$100))0,1,0))) Hope this helps! In article , "JBoulton" wrote: Domenic, Perfect! Thanks for the help. "Domenic" wrote: Try... E1, copied down: =SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/MAX(1,SUM(IF(FREQUENCY(IF((DAY($A $1:$A$100)=D1)*($B$1:$B$100<""),$A$1:$A$100),IF(( DAY($A$1:$A$100)=D1)*($ B$1:$B$100<""),$A$1:$A$100))0,1,0))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "JBoulton" wrote: All, a1:a1000 contains a list of dates including duplicates and b1:b1000 contains amounts. I need to calculate the average amount by day of the month. For example, 8/10/2004 2000 9/10/2004 1000 9/10/2004 5000 The total for the 10th day is 8000 and the average for the 10th day is 8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000 so the average of 2000 and 6000 is 4000. d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain the average by day. So, in this example, e10 = 6000. The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to get the denominator. |
Ron,
Thanks for the additional information. I didn't detect the potential problem you brought up because all of the data is in pairs, extracted from a database. I've settled on your original solution and adapted it to the dynamic range names in the detail worksheet. It's an elegant solution. "JBoulton" wrote: Ron, That's a great solution, too. Thanks for the lesson. "Ron Rosenfeld" wrote: On Wed, 23 Mar 2005 07:53:06 -0800, "JBoulton" wrote: All, a1:a1000 contains a list of dates including duplicates and b1:b1000 contains amounts. I need to calculate the average amount by day of the month. For example, 8/10/2004 2000 9/10/2004 1000 9/10/2004 5000 The total for the 10th day is 8000 and the average for the 10th day is 8000/2=4000. Or, on 8/10 the amount is 2000 and on 9/10 the amount is 6000 so the average of 2000 and 6000 is 4000. d1:d31 contains numbers 1 - 31 and e1:e31 (forming a table) should contain the average by day. So, in this example, e10 = 6000. The numerator is =sumproduct(--(day(a1:a1000)=d10)) but I'm stuck on how to get the denominator. Try this array function. To enter an array function, after copy/pasting it into the cell, hold <ctrl<shift while hitting <enter XL will place braces {...} around the formula: =IF(SUM(1*(DAY($A$1:$A$100)=D1))=0,"", SUM((DAY($A$1:$A$100)=D1)*$B$1:$B$100)/SUM(--(FREQUENCY( IF(DAY($A$1:$A$100)=D1,MATCH($A$1:$A$100,$A$1:$A$1 00,0)), ROW(INDIRECT("1:"&ROWS($A$1:$A$100))))0))) Note that this formula will produce a null string if there are no entries for a particular date. --ron |
On Wed, 23 Mar 2005 12:07:05 -0800, "JBoulton"
wrote: on, Thanks for the additional information. I didn't detect the potential problem you brought up because all of the data is in pairs, extracted from a database. I've settled on your original solution and adapted it to the dynamic range names in the detail worksheet. It's an elegant solution. Thank you for the feedback. I may have not been completely clear. When I said "no entries for a particular date", I meant that the date itself was missing. For example, if D1:D31 has the series 1...31, but in your data there is no 15th of the month, then the formula next to D15 will return a null string. However, if there is a 15th of the month in the data, but with no value entered next to it, the formula will assume a value of zero (0) for that date. Since your data is in pairs, this may not be a problem. If it is, logic could be added to test for that. --ron |
| All times are GMT +1. The time now is 06:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com