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