Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th column
not able to follow the logic of your formula. I am rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

the 6th column will be true if columns A, B and c are all blank and false if
they contain data. this column can then be used to determine if the running
total continues or it starts again froma value of zero.

the ISBLANK function could be added into the formula in the 5th column but
it would be harder to understant.

try adding the 6th column with the formula
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

and I think you will understand what it is doing. make column A - c blank
and it will show TRUE. Add data to any of the columns A - C and it will show
FALSE.

When two rows adjacent rows of FALSE are shown then the running total will
be set back to zero and a new running total will be calculated.

"belvy123" wrote:

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th column
not able to follow the logic of your formula. I am rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

Hi Again
It give me a Value# error when a row is blank

not sure why???

--
cruchnin numbers


"Joel" wrote:

the 6th column will be true if columns A, B and c are all blank and false if
they contain data. this column can then be used to determine if the running
total continues or it starts again froma value of zero.

the ISBLANK function could be added into the formula in the 5th column but
it would be harder to understant.

try adding the 6th column with the formula
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

and I think you will understand what it is doing. make column A - c blank
and it will show TRUE. Add data to any of the columns A - C and it will show
FALSE.

When two rows adjacent rows of FALSE are shown then the running total will
be set back to zero and a new running total will be calculated.

"belvy123" wrote:

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th column
not able to follow the logic of your formula. I am rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

I don't know which statement the error is occuring.

The =AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)) function should never give a
VALUE# error. You need to put this function on everyline including the blank
lines.


Therefore the error must be on the

=IF(AND(F1,F2),D3,E2+D3)
Let me know which row you are putting this line

This is designed to be on row 3.
The F1 is two rows before row 3
The F2 and E2 are on one row before row 3
And the D3 is on the same row which is row 3.

You can highlight the cell that has the error. Then go to Tools Menu -
formula Auditing - Evaluate formula. The press Evaluate until the error is
shown in the box. this will tell where the problem is. I think the problem
is that there isn'teither a True or false in every row on column 5.



"belvy123" wrote:

Hi Again
It give me a Value# error when a row is blank

not sure why???

--
cruchnin numbers


"Joel" wrote:

the 6th column will be true if columns A, B and c are all blank and false if
they contain data. this column can then be used to determine if the running
total continues or it starts again froma value of zero.

the ISBLANK function could be added into the formula in the 5th column but
it would be harder to understant.

try adding the 6th column with the formula
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

and I think you will understand what it is doing. make column A - c blank
and it will show TRUE. Add data to any of the columns A - C and it will show
FALSE.

When two rows adjacent rows of FALSE are shown then the running total will
be set back to zero and a new running total will be calculated.

"belvy123" wrote:

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th column
not able to follow the logic of your formula. I am rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

Hi Agian

they are in that order
but dont you get that value# error if you try to add say 1213422 + 0= value#
--
cruchnin numbers


"Joel" wrote:

I don't know which statement the error is occuring.

The =AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)) function should never give a
VALUE# error. You need to put this function on everyline including the blank
lines.


Therefore the error must be on the

=IF(AND(F1,F2),D3,E2+D3)
Let me know which row you are putting this line

This is designed to be on row 3.
The F1 is two rows before row 3
The F2 and E2 are on one row before row 3
And the D3 is on the same row which is row 3.

You can highlight the cell that has the error. Then go to Tools Menu -
formula Auditing - Evaluate formula. The press Evaluate until the error is
shown in the box. this will tell where the problem is. I think the problem
is that there isn'teither a True or false in every row on column 5.



"belvy123" wrote:

Hi Again
It give me a Value# error when a row is blank

not sure why???

--
cruchnin numbers


"Joel" wrote:

the 6th column will be true if columns A, B and c are all blank and false if
they contain data. this column can then be used to determine if the running
total continues or it starts again froma value of zero.

the ISBLANK function could be added into the formula in the 5th column but
it would be harder to understant.

try adding the 6th column with the formula
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

and I think you will understand what it is doing. make column A - c blank
and it will show TRUE. Add data to any of the columns A - C and it will show
FALSE.

When two rows adjacent rows of FALSE are shown then the running total will
be set back to zero and a new running total will be calculated.

"belvy123" wrote:

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th column
not able to follow the logic of your formula. I am rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

I check with the EXCEL help menu. The most likely cause in our case is if
text was entered instease of logical True or False

=AND("abc","123") this equation will produce the VALUE error. and expects
the parameters to be either true or false. It is possible you have double
quotes"" in an equation such as if("a1b2",,) instead of if(a1b2,,).

In our case that would occur if in the formula =IF(AND(F1,F2),D3,E2+D3) F1
or F2 contained text. Fheck the F column (the 6th column) and make sure
there is only True and False. Also make sure there isn't a 'true (single
quote in front of true) which would be a text string.

Adding 0 to a number will never give you and error
dividing by zero =100/0 gives a #div!0.

You need to check the formula where the Value Error is occuring. check all
the cell references and make sure those celss don't have Value Errors. for
example if the function is =and(A20,B20,C20). Make sure there are no errors
in cells A20, B20, and C20.

"belvy123" wrote:

Hi Agian

they are in that order
but dont you get that value# error if you try to add say 1213422 + 0= value#
--
cruchnin numbers


"Joel" wrote:

I don't know which statement the error is occuring.

The =AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)) function should never give a
VALUE# error. You need to put this function on everyline including the blank
lines.


Therefore the error must be on the

=IF(AND(F1,F2),D3,E2+D3)
Let me know which row you are putting this line

This is designed to be on row 3.
The F1 is two rows before row 3
The F2 and E2 are on one row before row 3
And the D3 is on the same row which is row 3.

You can highlight the cell that has the error. Then go to Tools Menu -
formula Auditing - Evaluate formula. The press Evaluate until the error is
shown in the box. this will tell where the problem is. I think the problem
is that there isn'teither a True or false in every row on column 5.



"belvy123" wrote:

Hi Again
It give me a Value# error when a row is blank

not sure why???

--
cruchnin numbers


"Joel" wrote:

the 6th column will be true if columns A, B and c are all blank and false if
they contain data. this column can then be used to determine if the running
total continues or it starts again froma value of zero.

the ISBLANK function could be added into the formula in the 5th column but
it would be harder to understant.

try adding the 6th column with the formula
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

and I think you will understand what it is doing. make column A - c blank
and it will show TRUE. Add data to any of the columns A - C and it will show
FALSE.

When two rows adjacent rows of FALSE are shown then the running total will
be set back to zero and a new running total will be calculated.

"belvy123" wrote:

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th column
not able to follow the logic of your formula. I am rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

Hi Agian

Bingo
That cured the Valu# error

But now it is carrying the last total amount all the way down the 5th column
instead of stopping if it see 2 blank rows in a row
I hope I am not being a pain
But thanks for all the help

--
cruchnin numbers


"Joel" wrote:

I check with the EXCEL help menu. The most likely cause in our case is if
text was entered instease of logical True or False

=AND("abc","123") this equation will produce the VALUE error. and expects
the parameters to be either true or false. It is possible you have double
quotes"" in an equation such as if("a1b2",,) instead of if(a1b2,,).

In our case that would occur if in the formula =IF(AND(F1,F2),D3,E2+D3) F1
or F2 contained text. Fheck the F column (the 6th column) and make sure
there is only True and False. Also make sure there isn't a 'true (single
quote in front of true) which would be a text string.

Adding 0 to a number will never give you and error
dividing by zero =100/0 gives a #div!0.

You need to check the formula where the Value Error is occuring. check all
the cell references and make sure those celss don't have Value Errors. for
example if the function is =and(A20,B20,C20). Make sure there are no errors
in cells A20, B20, and C20.

"belvy123" wrote:

Hi Agian

they are in that order
but dont you get that value# error if you try to add say 1213422 + 0= value#
--
cruchnin numbers


"Joel" wrote:

I don't know which statement the error is occuring.

The =AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)) function should never give a
VALUE# error. You need to put this function on everyline including the blank
lines.


Therefore the error must be on the

=IF(AND(F1,F2),D3,E2+D3)
Let me know which row you are putting this line

This is designed to be on row 3.
The F1 is two rows before row 3
The F2 and E2 are on one row before row 3
And the D3 is on the same row which is row 3.

You can highlight the cell that has the error. Then go to Tools Menu -
formula Auditing - Evaluate formula. The press Evaluate until the error is
shown in the box. this will tell where the problem is. I think the problem
is that there isn'teither a True or false in every row on column 5.



"belvy123" wrote:

Hi Again
It give me a Value# error when a row is blank

not sure why???

--
cruchnin numbers


"Joel" wrote:

the 6th column will be true if columns A, B and c are all blank and false if
they contain data. this column can then be used to determine if the running
total continues or it starts again froma value of zero.

the ISBLANK function could be added into the formula in the 5th column but
it would be harder to understant.

try adding the 6th column with the formula
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

and I think you will understand what it is doing. make column A - c blank
and it will show TRUE. Add data to any of the columns A - C and it will show
FALSE.

When two rows adjacent rows of FALSE are shown then the running total will
be set back to zero and a new running total will be calculated.

"belvy123" wrote:

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th column
not able to follow the logic of your formula. I am rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

ther ae only three reasons why it is not working

1) Are the blank rows showing both TRUE?
2) Check the row after the the two rows that contain true. Make sure the
tow cells inside the AND(F7,F8) are cells that contain the TRUE
3) Check the row after the the two rows that contain true. Make sure in the
formula =IF(AND(F6,F7),D8,E7+D8) that the second parameter D8 is correct. If
this formula is on row 8 (the row after the two TRUEs) that the second
paramter is D8. the D8 without the addtion sign is what starts the running
total back to zero. The E7 + D8 is what gives the running total when at
least one of the previous lines contains false

"belvy123" wrote:

Hi Agian

Bingo
That cured the Valu# error

But now it is carrying the last total amount all the way down the 5th column
instead of stopping if it see 2 blank rows in a row
I hope I am not being a pain
But thanks for all the help

--
cruchnin numbers


"Joel" wrote:

I check with the EXCEL help menu. The most likely cause in our case is if
text was entered instease of logical True or False

=AND("abc","123") this equation will produce the VALUE error. and expects
the parameters to be either true or false. It is possible you have double
quotes"" in an equation such as if("a1b2",,) instead of if(a1b2,,).

In our case that would occur if in the formula =IF(AND(F1,F2),D3,E2+D3) F1
or F2 contained text. Fheck the F column (the 6th column) and make sure
there is only True and False. Also make sure there isn't a 'true (single
quote in front of true) which would be a text string.

Adding 0 to a number will never give you and error
dividing by zero =100/0 gives a #div!0.

You need to check the formula where the Value Error is occuring. check all
the cell references and make sure those celss don't have Value Errors. for
example if the function is =and(A20,B20,C20). Make sure there are no errors
in cells A20, B20, and C20.

"belvy123" wrote:

Hi Agian

they are in that order
but dont you get that value# error if you try to add say 1213422 + 0= value#
--
cruchnin numbers


"Joel" wrote:

I don't know which statement the error is occuring.

The =AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)) function should never give a
VALUE# error. You need to put this function on everyline including the blank
lines.


Therefore the error must be on the

=IF(AND(F1,F2),D3,E2+D3)
Let me know which row you are putting this line

This is designed to be on row 3.
The F1 is two rows before row 3
The F2 and E2 are on one row before row 3
And the D3 is on the same row which is row 3.

You can highlight the cell that has the error. Then go to Tools Menu -
formula Auditing - Evaluate formula. The press Evaluate until the error is
shown in the box. this will tell where the problem is. I think the problem
is that there isn'teither a True or false in every row on column 5.



"belvy123" wrote:

Hi Again
It give me a Value# error when a row is blank

not sure why???

--
cruchnin numbers


"Joel" wrote:

the 6th column will be true if columns A, B and c are all blank and false if
they contain data. this column can then be used to determine if the running
total continues or it starts again froma value of zero.

the ISBLANK function could be added into the formula in the 5th column but
it would be harder to understant.

try adding the 6th column with the formula
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

and I think you will understand what it is doing. make column A - c blank
and it will show TRUE. Add data to any of the columns A - C and it will show
FALSE.

When two rows adjacent rows of FALSE are shown then the running total will
be set back to zero and a new running total will be calculated.

"belvy123" wrote:

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th column
not able to follow the logic of your formula. I am rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

Hi Agian

Ok Were on the right track here

Now how do I get it not to reset to total to zero but be able to carry it to
the next populated row of data

Thanks
--
cruchnin numbers


"Joel" wrote:

ther ae only three reasons why it is not working

1) Are the blank rows showing both TRUE?
2) Check the row after the the two rows that contain true. Make sure the
tow cells inside the AND(F7,F8) are cells that contain the TRUE
3) Check the row after the the two rows that contain true. Make sure in the
formula =IF(AND(F6,F7),D8,E7+D8) that the second parameter D8 is correct. If
this formula is on row 8 (the row after the two TRUEs) that the second
paramter is D8. the D8 without the addtion sign is what starts the running
total back to zero. The E7 + D8 is what gives the running total when at
least one of the previous lines contains false

"belvy123" wrote:

Hi Agian

Bingo
That cured the Valu# error

But now it is carrying the last total amount all the way down the 5th column
instead of stopping if it see 2 blank rows in a row
I hope I am not being a pain
But thanks for all the help

--
cruchnin numbers


"Joel" wrote:

I check with the EXCEL help menu. The most likely cause in our case is if
text was entered instease of logical True or False

=AND("abc","123") this equation will produce the VALUE error. and expects
the parameters to be either true or false. It is possible you have double
quotes"" in an equation such as if("a1b2",,) instead of if(a1b2,,).

In our case that would occur if in the formula =IF(AND(F1,F2),D3,E2+D3) F1
or F2 contained text. Fheck the F column (the 6th column) and make sure
there is only True and False. Also make sure there isn't a 'true (single
quote in front of true) which would be a text string.

Adding 0 to a number will never give you and error
dividing by zero =100/0 gives a #div!0.

You need to check the formula where the Value Error is occuring. check all
the cell references and make sure those celss don't have Value Errors. for
example if the function is =and(A20,B20,C20). Make sure there are no errors
in cells A20, B20, and C20.

"belvy123" wrote:

Hi Agian

they are in that order
but dont you get that value# error if you try to add say 1213422 + 0= value#
--
cruchnin numbers


"Joel" wrote:

I don't know which statement the error is occuring.

The =AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)) function should never give a
VALUE# error. You need to put this function on everyline including the blank
lines.


Therefore the error must be on the

=IF(AND(F1,F2),D3,E2+D3)
Let me know which row you are putting this line

This is designed to be on row 3.
The F1 is two rows before row 3
The F2 and E2 are on one row before row 3
And the D3 is on the same row which is row 3.

You can highlight the cell that has the error. Then go to Tools Menu -
formula Auditing - Evaluate formula. The press Evaluate until the error is
shown in the box. this will tell where the problem is. I think the problem
is that there isn'teither a True or false in every row on column 5.



"belvy123" wrote:

Hi Again
It give me a Value# error when a row is blank

not sure why???

--
cruchnin numbers


"Joel" wrote:

the 6th column will be true if columns A, B and c are all blank and false if
they contain data. this column can then be used to determine if the running
total continues or it starts again froma value of zero.

the ISBLANK function could be added into the formula in the 5th column but
it would be harder to understant.

try adding the 6th column with the formula
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

and I think you will understand what it is doing. make column A - c blank
and it will show TRUE. Add data to any of the columns A - C and it will show
FALSE.

When two rows adjacent rows of FALSE are shown then the running total will
be set back to zero and a new running total will be calculated.

"belvy123" wrote:

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th column
not able to follow the logic of your formula. I am rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

Hi Again


Here is what I used

=IF(AND(G4,G5),E5,E5+F4)

This works great.
It Took your help and guidance to get me here
Thanks again
DN
--
cruchnin numbers


"Joel" wrote:

ther ae only three reasons why it is not working

1) Are the blank rows showing both TRUE?
2) Check the row after the the two rows that contain true. Make sure the
tow cells inside the AND(F7,F8) are cells that contain the TRUE
3) Check the row after the the two rows that contain true. Make sure in the
formula =IF(AND(F6,F7),D8,E7+D8) that the second parameter D8 is correct. If
this formula is on row 8 (the row after the two TRUEs) that the second
paramter is D8. the D8 without the addtion sign is what starts the running
total back to zero. The E7 + D8 is what gives the running total when at
least one of the previous lines contains false

"belvy123" wrote:

Hi Agian

Bingo
That cured the Valu# error

But now it is carrying the last total amount all the way down the 5th column
instead of stopping if it see 2 blank rows in a row
I hope I am not being a pain
But thanks for all the help

--
cruchnin numbers


"Joel" wrote:

I check with the EXCEL help menu. The most likely cause in our case is if
text was entered instease of logical True or False

=AND("abc","123") this equation will produce the VALUE error. and expects
the parameters to be either true or false. It is possible you have double
quotes"" in an equation such as if("a1b2",,) instead of if(a1b2,,).

In our case that would occur if in the formula =IF(AND(F1,F2),D3,E2+D3) F1
or F2 contained text. Fheck the F column (the 6th column) and make sure
there is only True and False. Also make sure there isn't a 'true (single
quote in front of true) which would be a text string.

Adding 0 to a number will never give you and error
dividing by zero =100/0 gives a #div!0.

You need to check the formula where the Value Error is occuring. check all
the cell references and make sure those celss don't have Value Errors. for
example if the function is =and(A20,B20,C20). Make sure there are no errors
in cells A20, B20, and C20.

"belvy123" wrote:

Hi Agian

they are in that order
but dont you get that value# error if you try to add say 1213422 + 0= value#
--
cruchnin numbers


"Joel" wrote:

I don't know which statement the error is occuring.

The =AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)) function should never give a
VALUE# error. You need to put this function on everyline including the blank
lines.


Therefore the error must be on the

=IF(AND(F1,F2),D3,E2+D3)
Let me know which row you are putting this line

This is designed to be on row 3.
The F1 is two rows before row 3
The F2 and E2 are on one row before row 3
And the D3 is on the same row which is row 3.

You can highlight the cell that has the error. Then go to Tools Menu -
formula Auditing - Evaluate formula. The press Evaluate until the error is
shown in the box. this will tell where the problem is. I think the problem
is that there isn'teither a True or false in every row on column 5.



"belvy123" wrote:

Hi Again
It give me a Value# error when a row is blank

not sure why???

--
cruchnin numbers


"Joel" wrote:

the 6th column will be true if columns A, B and c are all blank and false if
they contain data. this column can then be used to determine if the running
total continues or it starts again froma value of zero.

the ISBLANK function could be added into the formula in the 5th column but
it would be harder to understant.

try adding the 6th column with the formula
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

and I think you will understand what it is doing. make column A - c blank
and it will show TRUE. Add data to any of the columns A - C and it will show
FALSE.

When two rows adjacent rows of FALSE are shown then the running total will
be set back to zero and a new running total will be calculated.

"belvy123" wrote:

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th column
not able to follow the logic of your formula. I am rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

We could eliminate column G by combining formulas. This would of been harder
to find the problems, but it would work. If you get a chance and look at
some of the questions posted, people have formulas that are over 200
characters long. Imagine how hard it is to solve these problems.

=IF(AND(A4,B4,C4,A5,B5,C5),E5,E5+F4)

When you started you didn't understand whatt the TRUE and FALSE where doing.
Now you are an expert!


"belvy123" wrote:

Hi Again


Here is what I used

=IF(AND(G4,G5),E5,E5+F4)

This works great.
It Took your help and guidance to get me here
Thanks again
DN
--
cruchnin numbers


"Joel" wrote:

ther ae only three reasons why it is not working

1) Are the blank rows showing both TRUE?
2) Check the row after the the two rows that contain true. Make sure the
tow cells inside the AND(F7,F8) are cells that contain the TRUE
3) Check the row after the the two rows that contain true. Make sure in the
formula =IF(AND(F6,F7),D8,E7+D8) that the second parameter D8 is correct. If
this formula is on row 8 (the row after the two TRUEs) that the second
paramter is D8. the D8 without the addtion sign is what starts the running
total back to zero. The E7 + D8 is what gives the running total when at
least one of the previous lines contains false

"belvy123" wrote:

Hi Agian

Bingo
That cured the Valu# error

But now it is carrying the last total amount all the way down the 5th column
instead of stopping if it see 2 blank rows in a row
I hope I am not being a pain
But thanks for all the help

--
cruchnin numbers


"Joel" wrote:

I check with the EXCEL help menu. The most likely cause in our case is if
text was entered instease of logical True or False

=AND("abc","123") this equation will produce the VALUE error. and expects
the parameters to be either true or false. It is possible you have double
quotes"" in an equation such as if("a1b2",,) instead of if(a1b2,,).

In our case that would occur if in the formula =IF(AND(F1,F2),D3,E2+D3) F1
or F2 contained text. Fheck the F column (the 6th column) and make sure
there is only True and False. Also make sure there isn't a 'true (single
quote in front of true) which would be a text string.

Adding 0 to a number will never give you and error
dividing by zero =100/0 gives a #div!0.

You need to check the formula where the Value Error is occuring. check all
the cell references and make sure those celss don't have Value Errors. for
example if the function is =and(A20,B20,C20). Make sure there are no errors
in cells A20, B20, and C20.

"belvy123" wrote:

Hi Agian

they are in that order
but dont you get that value# error if you try to add say 1213422 + 0= value#
--
cruchnin numbers


"Joel" wrote:

I don't know which statement the error is occuring.

The =AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)) function should never give a
VALUE# error. You need to put this function on everyline including the blank
lines.


Therefore the error must be on the

=IF(AND(F1,F2),D3,E2+D3)
Let me know which row you are putting this line

This is designed to be on row 3.
The F1 is two rows before row 3
The F2 and E2 are on one row before row 3
And the D3 is on the same row which is row 3.

You can highlight the cell that has the error. Then go to Tools Menu -
formula Auditing - Evaluate formula. The press Evaluate until the error is
shown in the box. this will tell where the problem is. I think the problem
is that there isn'teither a True or false in every row on column 5.



"belvy123" wrote:

Hi Again
It give me a Value# error when a row is blank

not sure why???

--
cruchnin numbers


"Joel" wrote:

the 6th column will be true if columns A, B and c are all blank and false if
they contain data. this column can then be used to determine if the running
total continues or it starts again froma value of zero.

the ISBLANK function could be added into the formula in the 5th column but
it would be harder to understant.

try adding the 6th column with the formula
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

and I think you will understand what it is doing. make column A - c blank
and it will show TRUE. Add data to any of the columns A - C and it will show
FALSE.

When two rows adjacent rows of FALSE are shown then the running total will
be set back to zero and a new running total will be calculated.

"belvy123" wrote:

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th column
not able to follow the logic of your formula. I am rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

forgot something in formula. This is hard to read and understand but works


=IF(AND(ISBLANK(A4),ISBLANK(B4),ISBLANK(C4),ISBLAN K(A5),ISBLANK(B5),ISBLANK(C5)),E5,E5+F4)

"Joel" wrote:

We could eliminate column G by combining formulas. This would of been harder
to find the problems, but it would work. If you get a chance and look at
some of the questions posted, people have formulas that are over 200
characters long. Imagine how hard it is to solve these problems.

=IF(AND(A4,B4,C4,A5,B5,C5),E5,E5+F4)

When you started you didn't understand whatt the TRUE and FALSE where doing.
Now you are an expert!


"belvy123" wrote:

Hi Again


Here is what I used

=IF(AND(G4,G5),E5,E5+F4)

This works great.
It Took your help and guidance to get me here
Thanks again
DN
--
cruchnin numbers


"Joel" wrote:

ther ae only three reasons why it is not working

1) Are the blank rows showing both TRUE?
2) Check the row after the the two rows that contain true. Make sure the
tow cells inside the AND(F7,F8) are cells that contain the TRUE
3) Check the row after the the two rows that contain true. Make sure in the
formula =IF(AND(F6,F7),D8,E7+D8) that the second parameter D8 is correct. If
this formula is on row 8 (the row after the two TRUEs) that the second
paramter is D8. the D8 without the addtion sign is what starts the running
total back to zero. The E7 + D8 is what gives the running total when at
least one of the previous lines contains false

"belvy123" wrote:

Hi Agian

Bingo
That cured the Valu# error

But now it is carrying the last total amount all the way down the 5th column
instead of stopping if it see 2 blank rows in a row
I hope I am not being a pain
But thanks for all the help

--
cruchnin numbers


"Joel" wrote:

I check with the EXCEL help menu. The most likely cause in our case is if
text was entered instease of logical True or False

=AND("abc","123") this equation will produce the VALUE error. and expects
the parameters to be either true or false. It is possible you have double
quotes"" in an equation such as if("a1b2",,) instead of if(a1b2,,).

In our case that would occur if in the formula =IF(AND(F1,F2),D3,E2+D3) F1
or F2 contained text. Fheck the F column (the 6th column) and make sure
there is only True and False. Also make sure there isn't a 'true (single
quote in front of true) which would be a text string.

Adding 0 to a number will never give you and error
dividing by zero =100/0 gives a #div!0.

You need to check the formula where the Value Error is occuring. check all
the cell references and make sure those celss don't have Value Errors. for
example if the function is =and(A20,B20,C20). Make sure there are no errors
in cells A20, B20, and C20.

"belvy123" wrote:

Hi Agian

they are in that order
but dont you get that value# error if you try to add say 1213422 + 0= value#
--
cruchnin numbers


"Joel" wrote:

I don't know which statement the error is occuring.

The =AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)) function should never give a
VALUE# error. You need to put this function on everyline including the blank
lines.


Therefore the error must be on the

=IF(AND(F1,F2),D3,E2+D3)
Let me know which row you are putting this line

This is designed to be on row 3.
The F1 is two rows before row 3
The F2 and E2 are on one row before row 3
And the D3 is on the same row which is row 3.

You can highlight the cell that has the error. Then go to Tools Menu -
formula Auditing - Evaluate formula. The press Evaluate until the error is
shown in the box. this will tell where the problem is. I think the problem
is that there isn'teither a True or false in every row on column 5.



"belvy123" wrote:

Hi Again
It give me a Value# error when a row is blank

not sure why???

--
cruchnin numbers


"Joel" wrote:

the 6th column will be true if columns A, B and c are all blank and false if
they contain data. this column can then be used to determine if the running
total continues or it starts again froma value of zero.

the ISBLANK function could be added into the formula in the 5th column but
it would be harder to understant.

try adding the 6th column with the formula
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

and I think you will understand what it is doing. make column A - c blank
and it will show TRUE. Add data to any of the columns A - C and it will show
FALSE.

When two rows adjacent rows of FALSE are shown then the running total will
be set back to zero and a new running total will be calculated.

"belvy123" wrote:

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th column
not able to follow the logic of your formula. I am rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

looks great thanks again
I will use it to eliminate the extra column
DN
--
cruchnin numbers


"Joel" wrote:

forgot something in formula. This is hard to read and understand but works


=IF(AND(ISBLANK(A4),ISBLANK(B4),ISBLANK(C4),ISBLAN K(A5),ISBLANK(B5),ISBLANK(C5)),E5,E5+F4)

"Joel" wrote:

We could eliminate column G by combining formulas. This would of been harder
to find the problems, but it would work. If you get a chance and look at
some of the questions posted, people have formulas that are over 200
characters long. Imagine how hard it is to solve these problems.

=IF(AND(A4,B4,C4,A5,B5,C5),E5,E5+F4)

When you started you didn't understand whatt the TRUE and FALSE where doing.
Now you are an expert!


"belvy123" wrote:

Hi Again


Here is what I used

=IF(AND(G4,G5),E5,E5+F4)

This works great.
It Took your help and guidance to get me here
Thanks again
DN
--
cruchnin numbers


"Joel" wrote:

ther ae only three reasons why it is not working

1) Are the blank rows showing both TRUE?
2) Check the row after the the two rows that contain true. Make sure the
tow cells inside the AND(F7,F8) are cells that contain the TRUE
3) Check the row after the the two rows that contain true. Make sure in the
formula =IF(AND(F6,F7),D8,E7+D8) that the second parameter D8 is correct. If
this formula is on row 8 (the row after the two TRUEs) that the second
paramter is D8. the D8 without the addtion sign is what starts the running
total back to zero. The E7 + D8 is what gives the running total when at
least one of the previous lines contains false

"belvy123" wrote:

Hi Agian

Bingo
That cured the Valu# error

But now it is carrying the last total amount all the way down the 5th column
instead of stopping if it see 2 blank rows in a row
I hope I am not being a pain
But thanks for all the help

--
cruchnin numbers


"Joel" wrote:

I check with the EXCEL help menu. The most likely cause in our case is if
text was entered instease of logical True or False

=AND("abc","123") this equation will produce the VALUE error. and expects
the parameters to be either true or false. It is possible you have double
quotes"" in an equation such as if("a1b2",,) instead of if(a1b2,,).

In our case that would occur if in the formula =IF(AND(F1,F2),D3,E2+D3) F1
or F2 contained text. Fheck the F column (the 6th column) and make sure
there is only True and False. Also make sure there isn't a 'true (single
quote in front of true) which would be a text string.

Adding 0 to a number will never give you and error
dividing by zero =100/0 gives a #div!0.

You need to check the formula where the Value Error is occuring. check all
the cell references and make sure those celss don't have Value Errors. for
example if the function is =and(A20,B20,C20). Make sure there are no errors
in cells A20, B20, and C20.

"belvy123" wrote:

Hi Agian

they are in that order
but dont you get that value# error if you try to add say 1213422 + 0= value#
--
cruchnin numbers


"Joel" wrote:

I don't know which statement the error is occuring.

The =AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)) function should never give a
VALUE# error. You need to put this function on everyline including the blank
lines.


Therefore the error must be on the

=IF(AND(F1,F2),D3,E2+D3)
Let me know which row you are putting this line

This is designed to be on row 3.
The F1 is two rows before row 3
The F2 and E2 are on one row before row 3
And the D3 is on the same row which is row 3.

You can highlight the cell that has the error. Then go to Tools Menu -
formula Auditing - Evaluate formula. The press Evaluate until the error is
shown in the box. this will tell where the problem is. I think the problem
is that there isn'teither a True or false in every row on column 5.



"belvy123" wrote:

Hi Again
It give me a Value# error when a row is blank

not sure why???

--
cruchnin numbers


"Joel" wrote:

the 6th column will be true if columns A, B and c are all blank and false if
they contain data. this column can then be used to determine if the running
total continues or it starts again froma value of zero.

the ISBLANK function could be added into the formula in the 5th column but
it would be harder to understant.

try adding the 6th column with the formula
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

and I think you will understand what it is doing. make column A - c blank
and it will show TRUE. Add data to any of the columns A - C and it will show
FALSE.

When two rows adjacent rows of FALSE are shown then the running total will
be set back to zero and a new running total will be calculated.

"belvy123" wrote:

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th column
not able to follow the logic of your formula. I am rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

To: Joel

I was browsing through the posts from this group just trying to learn a few
things and as a beginner, I am in complete awe of the patience, diligence and
competence exhibited by the people in this group. It is gratifying to know
there are still people in this world willing to help so many others not
blessed with the knowledge offered here.

Bob Morris

"Joel" wrote:

forgot something in formula. This is hard to read and understand but works


=IF(AND(ISBLANK(A4),ISBLANK(B4),ISBLANK(C4),ISBLAN K(A5),ISBLANK(B5),ISBLANK(C5)),E5,E5+F4)

"Joel" wrote:

We could eliminate column G by combining formulas. This would of been harder
to find the problems, but it would work. If you get a chance and look at
some of the questions posted, people have formulas that are over 200
characters long. Imagine how hard it is to solve these problems.

=IF(AND(A4,B4,C4,A5,B5,C5),E5,E5+F4)

When you started you didn't understand whatt the TRUE and FALSE where doing.
Now you are an expert!


"belvy123" wrote:

Hi Again


Here is what I used

=IF(AND(G4,G5),E5,E5+F4)

This works great.
It Took your help and guidance to get me here
Thanks again
DN
--
cruchnin numbers


"Joel" wrote:

ther ae only three reasons why it is not working

1) Are the blank rows showing both TRUE?
2) Check the row after the the two rows that contain true. Make sure the
tow cells inside the AND(F7,F8) are cells that contain the TRUE
3) Check the row after the the two rows that contain true. Make sure in the
formula =IF(AND(F6,F7),D8,E7+D8) that the second parameter D8 is correct. If
this formula is on row 8 (the row after the two TRUEs) that the second
paramter is D8. the D8 without the addtion sign is what starts the running
total back to zero. The E7 + D8 is what gives the running total when at
least one of the previous lines contains false

"belvy123" wrote:

Hi Agian

Bingo
That cured the Valu# error

But now it is carrying the last total amount all the way down the 5th column
instead of stopping if it see 2 blank rows in a row
I hope I am not being a pain
But thanks for all the help

--
cruchnin numbers


"Joel" wrote:

I check with the EXCEL help menu. The most likely cause in our case is if
text was entered instease of logical True or False

=AND("abc","123") this equation will produce the VALUE error. and expects
the parameters to be either true or false. It is possible you have double
quotes"" in an equation such as if("a1b2",,) instead of if(a1b2,,).

In our case that would occur if in the formula =IF(AND(F1,F2),D3,E2+D3) F1
or F2 contained text. Fheck the F column (the 6th column) and make sure
there is only True and False. Also make sure there isn't a 'true (single
quote in front of true) which would be a text string.

Adding 0 to a number will never give you and error
dividing by zero =100/0 gives a #div!0.

You need to check the formula where the Value Error is occuring. check all
the cell references and make sure those celss don't have Value Errors. for
example if the function is =and(A20,B20,C20). Make sure there are no errors
in cells A20, B20, and C20.

"belvy123" wrote:

Hi Agian

they are in that order
but dont you get that value# error if you try to add say 1213422 + 0= value#
--
cruchnin numbers


"Joel" wrote:

I don't know which statement the error is occuring.

The =AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)) function should never give a
VALUE# error. You need to put this function on everyline including the blank
lines.


Therefore the error must be on the

=IF(AND(F1,F2),D3,E2+D3)
Let me know which row you are putting this line

This is designed to be on row 3.
The F1 is two rows before row 3
The F2 and E2 are on one row before row 3
And the D3 is on the same row which is row 3.

You can highlight the cell that has the error. Then go to Tools Menu -
formula Auditing - Evaluate formula. The press Evaluate until the error is
shown in the box. this will tell where the problem is. I think the problem
is that there isn'teither a True or false in every row on column 5.



"belvy123" wrote:

Hi Again
It give me a Value# error when a row is blank

not sure why???

--
cruchnin numbers


"Joel" wrote:

the 6th column will be true if columns A, B and c are all blank and false if
they contain data. this column can then be used to determine if the running
total continues or it starts again froma value of zero.

the ISBLANK function could be added into the formula in the 5th column but
it would be harder to understant.

try adding the 6th column with the formula
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

and I think you will understand what it is doing. make column A - c blank
and it will show TRUE. Add data to any of the columns A - C and it will show
FALSE.

When two rows adjacent rows of FALSE are shown then the running total will
be set back to zero and a new running total will be calculated.

"belvy123" wrote:

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th column
not able to follow the logic of your formula. I am rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are both blank

You should have now for you running total in column E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows where I input numbers from
3 different presses production. in a 4th column I total that shifts
production and in a 5th column I have a running total of all rows that have
been entered.

What I need is a formula that if I have 1 row that is empty in all 3 columns
it will carry down the running total for that row and if it has 2 or more
rows where all 3 columns are blank that it will not carry down that running
total
--
crunchnin numbers

AGREED...
"robert morris" skrev i melding
...
To: Joel

I was browsing through the posts from this group just trying to learn a
few
things and as a beginner, I am in complete awe of the patience, diligence
and
competence exhibited by the people in this group. It is gratifying to
know
there are still people in this world willing to help so many others not
blessed with the knowledge offered here.

Bob Morris

"Joel" wrote:

forgot something in formula. This is hard to read and understand but
works


=IF(AND(ISBLANK(A4),ISBLANK(B4),ISBLANK(C4),ISBLAN K(A5),ISBLANK(B5),ISBLANK(C5)),E5,E5+F4)

"Joel" wrote:

We could eliminate column G by combining formulas. This would of been
harder
to find the problems, but it would work. If you get a chance and look
at
some of the questions posted, people have formulas that are over 200
characters long. Imagine how hard it is to solve these problems.

=IF(AND(A4,B4,C4,A5,B5,C5),E5,E5+F4)

When you started you didn't understand whatt the TRUE and FALSE where
doing.
Now you are an expert!


"belvy123" wrote:

Hi Again


Here is what I used

=IF(AND(G4,G5),E5,E5+F4)

This works great.
It Took your help and guidance to get me here
Thanks again
DN
--
cruchnin numbers


"Joel" wrote:

ther ae only three reasons why it is not working

1) Are the blank rows showing both TRUE?
2) Check the row after the the two rows that contain true. Make
sure the
tow cells inside the AND(F7,F8) are cells that contain the TRUE
3) Check the row after the the two rows that contain true. Make
sure in the
formula =IF(AND(F6,F7),D8,E7+D8) that the second parameter D8 is
correct. If
this formula is on row 8 (the row after the two TRUEs) that the
second
paramter is D8. the D8 without the addtion sign is what starts the
running
total back to zero. The E7 + D8 is what gives the running total
when at
least one of the previous lines contains false

"belvy123" wrote:

Hi Agian

Bingo
That cured the Valu# error

But now it is carrying the last total amount all the way down the
5th column
instead of stopping if it see 2 blank rows in a row
I hope I am not being a pain
But thanks for all the help

--
cruchnin numbers


"Joel" wrote:

I check with the EXCEL help menu. The most likely cause in our
case is if
text was entered instease of logical True or False

=AND("abc","123") this equation will produce the VALUE error.
and expects
the parameters to be either true or false. It is possible you
have double
quotes"" in an equation such as if("a1b2",,) instead of
if(a1b2,,).

In our case that would occur if in the formula
=IF(AND(F1,F2),D3,E2+D3) F1
or F2 contained text. Fheck the F column (the 6th column) and
make sure
there is only True and False. Also make sure there isn't a
'true (single
quote in front of true) which would be a text string.

Adding 0 to a number will never give you and error
dividing by zero =100/0 gives a #div!0.

You need to check the formula where the Value Error is
occuring. check all
the cell references and make sure those celss don't have Value
Errors. for
example if the function is =and(A20,B20,C20). Make sure there
are no errors
in cells A20, B20, and C20.

"belvy123" wrote:

Hi Agian

they are in that order
but dont you get that value# error if you try to add say
1213422 + 0= value#
--
cruchnin numbers


"Joel" wrote:

I don't know which statement the error is occuring.

The =AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)) function
should never give a
VALUE# error. You need to put this function on everyline
including the blank
lines.


Therefore the error must be on the

=IF(AND(F1,F2),D3,E2+D3)
Let me know which row you are putting this line

This is designed to be on row 3.
The F1 is two rows before row 3
The F2 and E2 are on one row before row 3
And the D3 is on the same row which is row 3.

You can highlight the cell that has the error. Then go to
Tools Menu -
formula Auditing - Evaluate formula. The press Evaluate
until the error is
shown in the box. this will tell where the problem is. I
think the problem
is that there isn'teither a True or false in every row on
column 5.



"belvy123" wrote:

Hi Again
It give me a Value# error when a row is blank

not sure why???

--
cruchnin numbers


"Joel" wrote:

the 6th column will be true if columns A, B and c are
all blank and false if
they contain data. this column can then be used to
determine if the running
total continues or it starts again froma value of zero.

the ISBLANK function could be added into the formula in
the 5th column but
it would be harder to understant.

try adding the 6th column with the formula
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

and I think you will understand what it is doing. make
column A - c blank
and it will show TRUE. Add data to any of the columns
A - C and it will show
FALSE.

When two rows adjacent rows of FALSE are shown then the
running total will
be set back to zero and a new running total will be
calculated.

"belvy123" wrote:

Thankyou for the reply Joel

I am not understanding the purpose of adding a 6th
column
not able to follow the logic of your formula. I am
rather new to this

Thanks
--
cruchnin numbers


"Joel" wrote:

I would add a new 6th column with the formula

for row 1 and copy to every row
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))

In the in the 5th column for the 1st and 2nd row I
would not change anything
starting in Row 3 I woud have

=IF(AND(F1,F2),D3,E2+D3)

And(F1,F2) will be true when row 1 and Row 2 are
both blank

You should have now for you running total in column
E the formula =E2+D3.



"belvy123" wrote:

Hi All


I have a sheet where I have 3 columns of 35 rows
where I input numbers from
3 different presses production. in a 4th column I
total that shifts
production and in a 5th column I have a running
total of all rows that have
been entered.

What I need is a formula that if I have 1 row
that is empty in all 3 columns
it will carry down the running total for that row
and if it has 2 or more
rows where all 3 columns are blank that it will
not carry down that running
total
--
crunchnin numbers