Report days of month with most sales

[Mike Martin]

Hello. I am looking for some ideas to help solve something I have been struggling with.

I have a history table which (for simplicity's sake) has 2 columns: Date and total sales (could just as easily be number of units produced/shipped/whatever) for the day. There is a row for each business day. What I am trying to do is show, over a 10 year period, which days of the month, within each month, are the highest amount of sales generated. For example: I want to be able to demonstrate "During January, the highest sales most often occur on the 10th, 15th, and 20th. During February ......" There are multiple days of the month with top sales because of the 10 year span and I would likely select the 3 dates in each month with most occurrences of max.

It has been fairly easy to create a small work table showing the highest sale of each month within each year (using a combination of MAX and array). What I have not been able to do is identify the day of each of the 120 months when the max occurred. This is where I am stuck and looking for help.

The results of this would become the input for a scatter chart where the name of each month would be on the Y axis and the day of the month on the X. (Instead of picking the top 3 days, I may end up showing the number of times max occurred on each day.)

I hope I made this clear enough.
Thanks in advance for any help you can offer.

 

[NARAYANK991]

Hi Mike ,

I am not able to understand your requirement ; if you have a matrix , where the months are the row headers , and the days from 1 through 31 are the column headers , each individual cell can be populated with a count of the number of times the maximum sales for the month have taken place on that date.

Is this what you want ?

Narayan

 

[Mike Martin]

Hi Mike ,

I am not able to understand your requirement ; if you have a matrix , where the months are the row headers , and the days from 1 through 31 are the column headers , each individual cell can be populated with a count of the number of times the maximum sales for the month have taken place on that date.

Is this what you want ?

Narayan

Thank you for your interest Narayan.

I guess I didn't make myself clear enough. Sorry. What you described is precisely what I am trying to get to. I agree from there, it will be easy.

I have attached a small mock up of the file to try to help clarify. Maybe I should have done this in the first place.

 

[NARAYANK991]

Hi Mike ,

See if this is acceptable.

Narayan

 

[Mike Martin]

Hi Mike ,

See if this is acceptable.

Narayan

Thanks again Narayan. You obviously put a lot of thought into devising that long formula. What I don't readily see is how to do a count of the hits within each date. But you may well have given me enough to work with so leave it with me. You applied a few functions I haven't experienced so it is time to learn.
Mike

 

[Mike Martin]

Problem solved! Using the formula you provided as a model I was able to come up with the solution I needed. I was aware MATCH and INDEX existed but have never had occasion to use them so I was unfamiliar with their use.

Narayan, you, and people like you, are to be commended for providing support by sharing your expertise. A big thank you.

Mike