Return Monthly Values
Posted by Fiona on November 05, 2001 2:34 PM
I have the following 2 cols of daily total sales from a small retail outlet:
Col A is DATE in format "01-Jan-01"
Col B is TAKINGS in format "$1,200"
Some days have zero takings (not too many I hope!)
Under this I want to have the ave daily takings for that month calculated, and the lowest daily takings, and the day that lowest taking occured, something like:
A400= "January" selected from listbox of months
A401= returns ave takings for month in A400
A402= returns min takings for month in A400
A403= returns date of min takings of that month in A400
I'd appreciate help with the formulas req'd in A401,402 and 403 cause I'm stuck.
Tks
Fiona
Posted by Aladin Akyurek on November 05, 2001 3:00 PM
Fiona --
In A401 enter: =SUMPRODUCT((TEXT(A1:A360, "mmmm") = A400) * (B1:B360))/MAX(1, SUMPRODUCT((TEXT(A1:A360, "mmmm") = A400)+0))
In A402 array-enter: =MIN(IF(TEXT(A1:A360, "mmmm") = A400,(B1:B360)))
In A403 enter: =INDEX(A1:A360, MATCH(A402,B1:B360,0))
Note 0. I assumed A1:A360 for dates and B1:B360 for takings. Adjust these to your situation.
Note 1. To array-enter the formula in A402, you need to hit CONTROL+SHIFT+ENTER at the same time, not just enter.
Note 2. The formula in A403 will retrieve the date that corresponds to the first token of the MIN value. If the MIN value is 40 e.g., for January, the same value may occur more than once in that month. It will then pick out the date that corresponds to the first occurrence of the MIN value.
If you'd like to have a copy of the workbook including all this, just drop me a line.
Aladin
Posted by Fiona on November 05, 2001 6:08 PM
Brilliant! Thanks Aladin!
An afterthought - if the lowest takings occur on 2 days in the same month, rather than just 1, how can I extract the second date of the equal min value?
Tks again - you're making me look good!
Fiona
Posted by Aladin Akyurek on November 06, 2001 10:34 AM
Re: One More!
Fiona --
I already feared that you'd want to have too!
In A404 enter: =IF(SUMPRODUCT((TEXT($A$1:$A$360, "mmmm") = $A$400) * ($B$1:$B$360=$A$402)) > ROW() - 403, INDEX($A$1:$A$360, MATCH($A$402, INDIRECT(ADDRESS(MATCH($A403, $A$1:$A$360, 0)+1, 2) & ":" & "$B$360"), 0) + MATCH($A403,$A$1:$A$360, 0)),"")
Copy this down till it doesn't produce a result.
I think you would want more, for example: spread wtr takings, coefficient of variation, etc. I'd suggest doing these computations somewhere else in the same worksheet or in another worksheet instead of in an area downwards adjacent to your data. That way these formula can be made more generic and a bit more efficient.
Regards,
Aladin
========= Brilliant! Thanks Aladin! An afterthought - if the lowest takings occur on 2 days in the same month, rather than just 1, how can I extract the second date of the equal min value? Tks again - you're making me look good! Fiona
Posted by Fiona on November 06, 2001 4:29 PM
All Done, Tks Aladin
Thankyou Aladin!
You've been a BIG help.
Fiona
