Thread: Time, Day and Criteria

Hey guys,

Got this spreadsheet I'm working on. What it does is populate a graph based off the time frame of multiple incidents occurring on a specific day.

My goal: To have Sheet2 tables populate the times automatically from Sheet3 based off set criteria.

Basically what I need is a formula in the Begin Time and End Time cells of the graph for Sunday through Saturday on Sheet2. I want this formula to pull the lowest time and the highest time from raw data on Sheet3 based off criteria in B2 and D5 on Sheet2. I need for days that Criteria in D5 is not met, to return blank. This way it maps out the overall time of all incidents for criteria in D5 and places the time frame on the days that criteria in D5 is shown.

So for example if Sunday, Monday, Wednesday are the days that "Case 3" (Sheet2 D5) shows up on Sheet3 raw data. I want the total time from all incidents containing Case3 to populate on only Sunday, Monday, Wednesday, and leave all the rest blank since Case 3 does not show on those days.

Since the times on my graph all end in "00" is there a way to round down the time to end in "00". So if it comes back as 11:50 can the formula round down to 11:00?

Thanks in advance!

Mothman, can you clarify as to why a large number of the values in Sheet3!A:A are blank ?

In addition, can you subsequently clarify the signifance of the blank - ie what it represents / implies - ie that the Case is that last referenced or that it should be ignored in the latter calculations ?

Basically I want the formula to find what's typed into Sheet2!D5 on Sheet3!A:A, then to find what is typed into Sheet2!B2 on Sheet3!B:B. If there is a match, then it will give the MIN and MAX number of Sheet3!E:E based off the criteria entered. These results will only populate the table on the days which match case name entered into Sheet2!D5. So if it was Case 3 for Loc13 occurred on Sunday, Saturday, Monday, then the MIN and MAX results of Sheet3!E:E will populate for only Sunday, Saturday, and Monday, thus leaving the other days blank or "0:00". Do you think this is possible? Thanks for the response.


EDIT: I don't wan't the MIN MAX to be day specific though. I want it to look at all of the results based off criteria of Sheet2!D5 and Sheet2!B2 then give the min max total, then imput that onto the days related. So if incidents were on Sunday 11:00, Saturday 13:00, Monday 15:00 the result would be 11:00-15:00, and would then input into cells for Sunday, Saturday and Monday, leaving the rest blank or "0:00".

Sorry if I can't explain it correctly, it's kind of complicated what I'm asking for.

Mothman, I understand your overall aim.

What I asked in my prior post was specifically related to the significance of the blanks in Col A on sheet3.

Until such time as that query is answered anything that I or anyone else may choose to offer is going to based upon guesswork.

OK, maybe I was over confident... I *thought* I understood what you were trying to do based on your OP but your latter posts make me question that.

You don't really specify where your results are meant to be recorded... and I can't really tell if the values in your sample (D7:E7) were meant to represent desired output or not ?

What I would say is that without additional cells on Sheet3 to ease the burden you're looking at an inefficient approach, eg:

B7:
=MIN(IF((Sheet3!$A$1:$A$200=$D$5)*(Sheet3!$B$1:$B$200=$B$2),Sheet3!$E$1:$E$200))*(SUM(IF((Sheet3!$A$1:$A$200=$D$5)*(Sheet3!$B$1:$B$200=$B$2)*(TEXT(Sheet3!$D$1:$D$200,"dddd")=$A7),1))>0)
confirmed with CTRL + SHIFT + ENTER

the above, copied to B13, would return the MIN time for the combination - returning 0 on those days where the combination did not exist
(you could use a Custom Format to hide this assuming 0 (midnight) is never a valid result)

the same formula, changed to MAX (remember to reset Array), would generate your End Time(s)

If you need to round down you could encase the above within a FLOOR function, eg:

=FLOOR(above,"01:00")
confirmed with CTRL + SHIFT + ENTER

Assuming, as I suspect, the above is incorrect in terms of your real goal - please post a sample which outlines the expected/desired results based on the sample data (calculated manually obviously). This way people can validate the logic of their suggestions.


On a final note: though you can use entire column references in Arrays in XL2007 and beyond (you can't in earlier versions) - you shouldn't ... Arrays (& SUMPRODUCT) are inefficient - used over such large datasets they will cripple performance.
If needed use Dynamic Named Ranges - but try to avoid using OFFSET to generate them - esp. if using in conjunction with Arrays / Sumproducts - use INDEX instead.

You can replace the multiplier and use a MATCH test such that instead of returning 0 in cases of no match an #N/A result is returned instead, eg:

B7:
=FLOOR(MIN(IF((Sheet3!$A$1:$A$200=$D$5)*(Sheet3!$B$1:$B$200=$B$2),Sheet3!$E$1:$E$200))*(MATCH(1,(Sheet3!$A$1:$A$200=$D$5)*(Sheet3!$B$1:$B$200=$B$2)*(TEXT(Sheet3!$D$1:$D$200,"dddd")=$A7),0)>0),"01:00")
confirmed with CTRL + SHIFT + ENTER
copied down

C7: (as above but using CEILING & MAX
=CEILING(MAX(IF((Sheet3!$A$1:$A$200=$D$5)*(Sheet3!$B$1:$B$200=$B$2),Sheet3!$E$1:$E$200))*(MATCH(1,(Sheet3!$A$1:$A$200=$D$5)*(Sheet3!$B$1:$B$200=$B$2)*(TEXT(Sheet3!$D$1:$D$200,"dddd")=$A7),0)>0),"01:00")
confirmed with CTRL + SHIFT + ENTER
copied down

Edit: Didn't read. Stick to DO's answer.

Ricardo, if you read between the lines I think we can assume that the NA() is required for charting purposes, a custom format won't suffice.

If we accept that fact, given the multiplier is required regardless, it's simplest to modify to a MATCH based test thereby ensuring NA() rather than 0 for non-match.

EDIT: I could be wrong of course - most people today have been assuming that fact...

Er, did you see post # 10 ?