I am working on a spread sheet for truck drivers. I have then columns set up
with the drivers names and hours of work. The rows are broke up into days of
the week. Drivers are only allowed to driver 70 hours in 8 days. I have the
spread sheet set so the column is subtracting the given hours from the 70 to
show how many hours are left in the prior 8 day period. Only problem is when
I driver doesn't work the time needs to reset to the available 70. I have an
IF formula that is working, but it is set to count the days above the last
zero. How can I have it stop that?
=IF(C19=0,-70,c19+c18+c17+c16+c15+c14+c13+c12-70)
I need to get it to stop counting above the last zero.

Try

=SUM(C19:INDEX(C12:C19,MAX(IF(C12:C19=0,ROW(C12:C1 9)))-ROW(C12)+1))

as an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"wvhilltop" wrote in message
...
I am working on a spread sheet for truck drivers. I have then columns set
up
with the drivers names and hours of work. The rows are broke up into days
of
the week. Drivers are only allowed to driver 70 hours in 8 days. I have
the
spread sheet set so the column is subtracting the given hours from the 70
to
show how many hours are left in the prior 8 day period. Only problem is
when
I driver doesn't work the time needs to reset to the available 70. I have
an
IF formula that is working, but it is set to count the days above the last
zero. How can I have it stop that?
=IF(C19=0,-70,c19+c18+c17+c16+c15+c14+c13+c12-70)
I need to get it to stop counting above the last zero.

When I copied and pasted it in the correct cell, I get #VALUE, but when I ask
to look at the function it gives me the correct answer. So it knows it, what
am I doing to keep it from displaying properly? I don't understand the array
thing, how does that come in?

"Bob Phillips" wrote:

Try

=SUM(C19:INDEX(C12:C19,MAX(IF(C12:C19=0,ROW(C12:C1 9)))-ROW(C12)+1))

as an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"wvhilltop" wrote in message
...
I am working on a spread sheet for truck drivers. I have then columns set
up
with the drivers names and hours of work. The rows are broke up into days
of
the week. Drivers are only allowed to driver 70 hours in 8 days. I have
the
spread sheet set so the column is subtracting the given hours from the 70
to
show how many hours are left in the prior 8 day period. Only problem is
when
I driver doesn't work the time needs to reset to the available 70. I have
an
IF formula that is working, but it is set to count the days above the last
zero. How can I have it stop that?
=IF(C19=0,-70,c19+c18+c17+c16+c15+c14+c13+c12-70)
I need to get it to stop counting above the last zero.

Normally, when you add the formula, you then hit Enter, and Excel resolves
it. With an array formula, you must use the Ctrl and Shift and Enter keys
all at the same time to tell excel that it is an array formula. This will
apply if you later update it.

If you get it right, Excel will surround the formula with braces {...}, so
it will look like

{=SUM(C19:INDEX(C12:C19,MAX(IF(C12:C19=0,ROW(C12:C 19)))-ROW(C12)+1))}

When you edit the formula, you wont see those braces, they will get
re-instated when you later Ctrl-Shift-Enter it.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"wvhilltop" wrote in message
...
When I copied and pasted it in the correct cell, I get #VALUE, but when I
ask
to look at the function it gives me the correct answer. So it knows it,
what
am I doing to keep it from displaying properly? I don't understand the
array
thing, how does that come in?

"Bob Phillips" wrote:

Try

=SUM(C19:INDEX(C12:C19,MAX(IF(C12:C19=0,ROW(C12:C1 9)))-ROW(C12)+1))

as an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"wvhilltop" wrote in message
...
I am working on a spread sheet for truck drivers. I have then columns
set
up
with the drivers names and hours of work. The rows are broke up into
days
of
the week. Drivers are only allowed to driver 70 hours in 8 days. I
have
the
spread sheet set so the column is subtracting the given hours from the
70
to
show how many hours are left in the prior 8 day period. Only problem
is
when
I driver doesn't work the time needs to reset to the available 70. I
have
an
IF formula that is working, but it is set to count the days above the
last
zero. How can I have it stop that?
=IF(C19=0,-70,c19+c18+c17+c16+c15+c14+c13+c12-70)
I need to get it to stop counting above the last zero.

Thank you, that works great!!!!

"Bob Phillips" wrote:

Normally, when you add the formula, you then hit Enter, and Excel resolves
it. With an array formula, you must use the Ctrl and Shift and Enter keys
all at the same time to tell excel that it is an array formula. This will
apply if you later update it.

If you get it right, Excel will surround the formula with braces {...}, so
it will look like

{=SUM(C19:INDEX(C12:C19,MAX(IF(C12:C19=0,ROW(C12:C 19)))-ROW(C12)+1))}

When you edit the formula, you wont see those braces, they will get
re-instated when you later Ctrl-Shift-Enter it.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"wvhilltop" wrote in message
...
When I copied and pasted it in the correct cell, I get #VALUE, but when I
ask
to look at the function it gives me the correct answer. So it knows it,
what
am I doing to keep it from displaying properly? I don't understand the
array
thing, how does that come in?

"Bob Phillips" wrote:

Try

=SUM(C19:INDEX(C12:C19,MAX(IF(C12:C19=0,ROW(C12:C1 9)))-ROW(C12)+1))

as an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"wvhilltop" wrote in message
...
I am working on a spread sheet for truck drivers. I have then columns
set
up
with the drivers names and hours of work. The rows are broke up into
days
of
the week. Drivers are only allowed to driver 70 hours in 8 days. I
have
the
spread sheet set so the column is subtracting the given hours from the
70
to
show how many hours are left in the prior 8 day period. Only problem
is
when
I driver doesn't work the time needs to reset to the available 70. I
have
an
IF formula that is working, but it is set to count the days above the
last
zero. How can I have it stop that?
=IF(C19=0,-70,c19+c18+c17+c16+c15+c14+c13+c12-70)
I need to get it to stop counting above the last zero.

Try a simple if function and use the results as your carry foreward
subtotal.

in D12 enter .....=IF(C12=0,70,70-C12)
in D13 enter .....=IF(C13=0,70,D12-C13)
extend D13 down to D19 using the right hand bottom corner of d13.

Trying Bob's example, it looks like you have to have all the days filled in
before you get a answer.

Greetings from New Zealand

"Bob Phillips" wrote in message
...
Try

=SUM(C19:INDEX(C12:C19,MAX(IF(C12:C19=0,ROW(C12:C1 9)))-ROW(C12)+1))

as an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"wvhilltop" wrote in message
...
I am working on a spread sheet for truck drivers. I have then columns set
up
with the drivers names and hours of work. The rows are broke up into
days of
the week. Drivers are only allowed to driver 70 hours in 8 days. I have
the
spread sheet set so the column is subtracting the given hours from the 70
to
show how many hours are left in the prior 8 day period. Only problem is
when
I driver doesn't work the time needs to reset to the available 70. I
have an
IF formula that is working, but it is set to count the days above the
last
zero. How can I have it stop that?
=IF(C19=0,-70,c19+c18+c17+c16+c15+c14+c13+c12-70)
I need to get it to stop counting above the last zero.

The formula in D12 looks equivalent to =70-C12 (as the IF doesn't seem to do
anything different?).
--
David Biddulph

"Bill Kuunders" wrote in message
...
Try a simple if function and use the results as your carry foreward
subtotal.

in D12 enter .....=IF(C12=0,70,70-C12)
....

You're absolutely right.

Thanks
Bill K
"David Biddulph" wrote in message
...
The formula in D12 looks equivalent to =70-C12 (as the IF doesn't seem to
do anything different?).
--
David Biddulph

"Bill Kuunders" wrote in message
...
Try a simple if function and use the results as your carry foreward
subtotal.

in D12 enter .....=IF(C12=0,70,70-C12)
...