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

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

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

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

Hi John

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

Regards

Roger Govier


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

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

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:C 33),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: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

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

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

Hi Roger

Just sent

Rgds

John

"Roger Govier" wrote in message
...
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(C1 8: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:C 33,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

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

Just sent

Rgds

John

"Roger Govier" wrote in message
...

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(C1 8: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(C 18: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: 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

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


"Roger Govier" wrote in message
...
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 wrote:
Hi Roger

Just sent

Rgds

John

"Roger Govier" wrote in message
...

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(C 18: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 :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