How can I correct this Time formula

[John]

How can I correct this Time formula which says in the Tag that it produces a
negative Result and hence displays in cell as ####

=(INDEX($A18:$A33,MATCH(MIN(C18:C33),C18:C33,0)))-(60/1440)

What I am doing is selecting the field in A which equates to the lowest
value in C (C= hourly sales), then I am subtracting 60 mins from the value
selected in A. This in effect will give me my closing Times i.e. find the
cell with Zero Sales then subtract 1 hour to find what must be the closing
time (assuming of course that there is at least ?1 of sales per hour while
open).

My cells in A are formatted as h:mm AM/PM, as is the format in the formula
cell

Thanks

 

[Bob Phillips]

The min is clearly less than 1 hour, so what do you want to do in those
circumstances, zeroise the result, or take the absolute value.

If the former, which makes mores sense to me, use

=MAX(TIME(1,0,0),INDEX($A18:$A33,MATCH(MIN(C18:C33),C18:C33,0)))-(60/1440)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[John]

Thanks Bob, yes it appears I'm subtracting 60 mins from 12:00am which gives
a negative, i.e. anywhere I expect an 11:00pm closing time

Strange thing on your formula Bob is that its correct when closing time (or
last sales hour) is 12.00am or latter, but its 1 hour out when the closing
time (or last sale hour) is before 12:00am eg. Expect to see 11:00pm, but
formula returns 12:00am

 

[Bob Phillips]

John,

I am not seeing that. I just adjusted my data to make midnight the lowest
amount, and it is returning 23:00.

Odd or what?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Roger Govier]

Hi John

Formula works fine for me.
What does your data look like?

Regards

Roger Govier

 

[John]

Guys

My data in hourly segments, from 12:00am to 6:00am shows Zero (based on a
formula in each cell, see below) but even when I copy-paste special values
the formula it still returns Zero, so there are no hidden decimal values.

My last value (greater than zero) is in the 11:00pm cell, so next value cell
is 12:00am which = Zero, thus minus 60 mins should be 11:00pm, but returns
12:00am

Am I doing something wrong? I'm assuming that the other Zeros which are in
the slots represented by 1:00am to 6:00am are not effecting it as other days
seem to be returning the expected value

BTW I'm working data just for Monday, just incase my last sentence seems
confusing. Below is the Data which I have (for Monday- Times are in Col A,
Sales are in Col B) so I'm expecting Closing Time i.e. the formula to return
11:00pm, but it shows 12:00am

Just to be sure my formula is -
=MAX(TIME(1,0,0),INDEX($A18:$A33,MATCH(MIN(C18:C33),C18:C33,0)))-(60/1440)


10:00 PM ?163
11:00 PM ?82
12:00 AM ?0
1:00 AM ?0
2:00 AM ?0
3:00 AM ?0
4:00 AM ?0
5:00 AM ?0
6:00 AM ?0

 

[Roger Govier]

Hi John

With that set of data and with your original formula as opposed to the one
posted by Bob, I get the result 11:00 PM

I also get exactly the same result with Bob's formula.

Windows Xp, Excel 2003

Regards

Roger Govier

 

[John]

Roger

I'm lost, with both my original and Bob's I'm getting a negative value,
hence ####.

I'm running WinXp Excel 2002

 

[Roger Govier]

Hi John

Email me a copy of your file.
Take NOSPAM out of my email address to send direct.

Regards

Roger Govier

 

[John]

Hi Roger

Just sent

Rgds

John

Hi John

Email me a copy of your file.
Take NOSPAM out of my email address to send direct.

Regards

Roger Govier

 

[Roger Govier]

Hi John

You are going to kick yourself.
The time value selected in A27 is 12:00 midnight from your dropdown list in
Masters, not 12:00 noon.
Excel therefore sees this as a negative time calculation.
I found it because I first wrapped the formula inside a MOD() function

=MOD((INDEX($A18:$A33,MATCH(MIN(C18:C33),C18:C33,0)))-(60/1440),1)

It returned the answer of 11:00 (albeit it was obviously 11:00 pm) which
lead me to look at the time value you had entered in A27 because using MOD
is a way of overcoming negative time values.

Regards

Roger Govier

 

[John]

Thanks Roger, I knew that it was essentially 0:00 or zero, but didn't know
how to effectively subtract in time, when it was 0:00. The MOD function, at
least I know a use for it now

Thanks again

Rgds

John