I\'m trying to do some MIN calculations, working out the shortest call per day f
ID: 3563112 • Letter: I
Question
I'm trying to do some MIN calculations, working out the shortest call per day for a 5 day period.
I've tried a few different formulas and am getting confusing results:
{=(Data!$H:$H>0)*(MIN(IF(Data!$C:$C=1,Data!$H3:$H6000)))}
The $C:$C=1 indicates Day 1 of our data. Previously I was using a date and was finding this caused a VALUE error with this and within my MAX IF formulas. For some reason this formula is still returning the correct 00:00:01 result when entered as a normal formula, and an incorrect 00:00:00 when entered as an array formula (Ctrl+Alt+Enter).
However the adjacent row still returns an incorrect 00:00:00 result however it is entered. I've checked multiple times to ensure there isn't a stray 00:00:00 formula in thes datta sheets there definitely isn't one that could be being read as the correct minimum.
All I want it to do is check for the shortest time valuee in a select column, if another column contains the correct value of 1,2, etc. corresponding to the day of the audi!tt.
Explanation / Answer
Try this one
=MIN(IF($C$3:$C$6000=1,IF($H$3:$H$6000<>"",$H$3:$H$6000),""))
Note the correct way to enter it is Ctlt + Shift + Enter
This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.