subtracting cells from next value

[The Fly]

Hey everyone,

I have a spreadsheet with 3 columns.. Amount, base, paid

The amount is total produced

Base-- base pay (draw)

Paid-- amount comm paid

A B C
100,000 1000 14,000-(B1)

BLANK 1000 BLANK

100,000 1000 35,000-(B2+B3)


FOR EVERY BLANK.. I am wanting to sum each 1,000, including the next 1,000 on a non-blank row and subtract from the next "paid" amount

I am having trouble coming up with the concept..

any help would be appreciated

[gmr4evr1]

Could you use this....

Please Login or Register  to view this content.

Change the ranges to suit your needs. This will get you the sum of the 1000 with blanks, but will not give the subtract from part of it as I do not know the range to subtract.

[The Fly]

This may work but...

Not sure

I want the following to happen

If column a has an amount then column c will have an amount
And that amount will subtract column b

Column a | column b | column c
100,000. |. 1000. | 17,000 (cell C1- B1)
Blank. |. 1000. |. Blank
Blank. |. 1000. |. Blank
100,000. |. 1000. | 31,000. (Cell C4 - sum(Cell B2:B4)
Blank. |. 1000. | blank.
100,000. |. 1000. |35,000. (Cell C6 -sum(B5:B6)

Thanks for your help!

[gmr4evr1]

If I understand correctly, you want to subtract the value in column C with the value in column B, BUT, depending on how many blanks there are before each non-blank row, will determine which cells in column B will be used to subtract from column C? If this is correct, then I have no clue how to go about doing that.

[The Fly]

That's correct... Thanks

[gmr4evr1]

Hopefully one of the infamous guru's/experts can help you out.

[Beamernsw]

Hi, hopefully the attached worksheet will do what you're looking for.
Cells in column D need to be entered as an array (Ctrl-Shift-Enter).
And Row 1 needs to be empty.

[The Fly]

BEamernsw,

Thank you for the doc.. it almost works.. I had to add some column to my table and I am getting the following...

A LITTLE CONFUSED AS TO WHY THIS IS HAPPENING.

Thanks for your help!!

[Beamernsw]

Hi, its because my formula is checking if the cell in column F is blank, but you have a 0 in there.

So change the first "" to 0 and if you wish to have a 0 in column G as well then also change the last "" to 0.

Please Login or Register  to view this content.