Need Formula for what time of day are sales the highest or lowest

[rmccain]

Hello,

I am trying to complete a school project that will tell me the following:

I need the formula to tell me what time of day the peak sales and the low sales for each day occur. Please see below how the data looks

1. I have 1 years worth of department store HOURLY data.

2. The store opens at 10 am and closes at 6 pm

3. Every hour the stores sales are recorded as follows:


Thankyou


A B C

DATE Time Dollars
01/01/2014 1100 50

01/01/2014 1200 150

01/01/2014 1300 250

01/01/2014 1400 500

01/01/2014 1500 800

01/01/2014 1600 700

01/01/2014 1700 500

01/01/2014 1800 800


01/02/2014 1000 100

01/02/2014 1100 100

01/02/2014 1200 150

01/02/2014 1300 200

01/02/2014 1400 250

01/02/2014 1500 800

01/02/2014 1600 800

01/02/2014 1700 900

01/02/2014 1800 500

ETC....

[sktneer]

It depends on the data you have. For example if you have only one max and one min sales amount for each day, it would be simple to know the time for max and min sales.

If this is the case.........

For time of Max sale...........

Please Login or Register  to view this content.

For time of Min Sale...........

Please Login or Register  to view this content.

But if you have multiple occurrences of Max and Min sales for any day, you will need an Array Formula to get all the time slots for the max and min sales for a day....

So if this is the case.........
Since both of the following formulas are Array Formulas, so you need to confirm them with Ctrl + Shift + Enter instead of just Enter. (i.e. hold down the Ctrl + Shift and then press Enter)

For Max sale......

Please Login or Register  to view this content.

and then copy down until you get blank cells.


For Min sale...........

Please Login or Register  to view this content.

and copy down until you get blank cell.

[rmccain]

Hello,


Firstly, Thankyou for your time; it is greatly appreciated !!

I cannot get your formula to work correctly.

I have attached a more detailed sample file of what I am trying to accomplish.

Please note that:

2 High times or 2 Low times can occur on the same day. It is not probable but can happen.

THANKYOU again !

Please see attachment

[zbor]

In example in first post you have 3 columns (ABC)
In example in attachemnt you have one column, separated by ,

What is your input?

[rmccain]

Hello please see new attachment.

It should have the following rows:

DATE TIME HIGH LOW HIGH TIME LOW TIME

There are no inputs I inputted by hand what I would like extracted into the High Time and Low Time Cell.

What I am trying to accomplish is write the formula that will tell me the time of high value and time of the low value each day and record the time under high time and low time like in the attachment.

I will then use the countif function to tell me what are the highest and lowest times , in sequence , from top to bottom the times that the highest sales volume occurs and the time the lowest sales volume occurs. ( I know how to do this but not the first part)

[sktneer]

As per your sheet layout, try these Array Formulas to get the MIN and MAX Time values. Since these are array formula so you need to confirm them with Ctrl+Shift+Enter instead of just Enter. (i.e. hold down the Ctrl+Shift and then press Enter.)

For MAX Time, in F2

Please Login or Register  to view this content.

and then copy down.

For MIN Time, in H2

Please Login or Register  to view this content.

and then copy down.

For detail see the attached sheet.

[rmccain]

Ha,

It works !

Many thanks for your time !


Regards

[sktneer]

Glad to help you. Thanks for the feedback.