Hi John

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

Regards

Roger Govier


John wrote:
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" wrote in message
...

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 wrote:

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




"Bob Phillips" wrote in message
...


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)


"John" wrote in message
...


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" wrote in message
. ..


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" wrote in message
...


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:C3 3,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