Hiding balance in unused rows

Topic

New Reply

I’m trying to set up a new spreadsheet to keep track of payroll. Row 2 is the first row where values are entered. Column A is the date, Column B is the gross pay, Column C is Federal withholding, Column D is Medicare tax, Column E is SS tax, Column F is State withholding, Column G is Net Pay (=B2-C2-D2-E2-F2). I also want a Column H for Year to date gross. To keep a running total in H without the balance showing in rows with no values entered yet, I put the following formula in H2: =SUM(B2) and the following formula in H3: =IF(B3=””,””,B3+H2). This gives me a balance in H3 without that number appearing in every row under H3. Now here’s the problem: I also want a Column I for Year to date net, where the formula for I2 is =SUM(G2) and the formula for I3 is =IF(G3=””,””,G3+I2). It’s the same formula I have starting in H3. BUT I’m getting the running balance in every row under I3. If the formula in H3 works the way I want it to, why not the one in I3?

Reply | Quote

Replies

Hi

..it’s because G3 isn’t blank! It’s got a formula in it!
Use this instead in cell [I3]:
=IF(B3=””,””,G3+I2).

or see attached file.

zeddy

Reply | Quote

..it’s because G3 isn’t blank! It’s got a formula in it!

This is not strictly true. It is not that the cells in G have a formula, it is because the formulas will never yield a null string(“”) but will always yield a number (=B2-C2-D2-E2-F2). With your modification to the G3 formula to display a null, the original formula using G3 will work.

Steve
PS Glad to have you back. I hope you are feeling better! No longer all wrapped up with no place to go…

Reply | Quote

Hey thanks! That fixed it.

Reply | Quote

Hi Steve

What I meant to say was
Hi

..it’s because G3 isn’t blank! It’s got a formula in it which will never yield a null string!

It was sloppy on my part, a bit like my insides. Not quite in full working order yet.

zeddy

Reply | Quote