Subtotal

[bgo]

Hi,

I'm trying to create a spreadsheet using the subtotal formula. The goal is to use the current balance to calculate new balances at every credit/debit. However, I ran into the problem below:

Credit
/Debit Balance
100
20 120 =SUBTOTAL(9,OFFSET(B4,-1,0),OFFSET(B4,0,-1))
-30 -30 =SUBTOTAL(9,OFFSET(B5,-1,0),OFFSET(B5,0,-1))
50 50 =SUBTOTAL(9,OFFSET(B6,-1,0),OFFSET(B6,0,-1))


Now, the first subtotal result -- the value in cell B4 -- is correct (100 + 20 = 120); however, cells B5 and B6 doesn't add the cell immediately above and the cell immediately to the left, as referred to in OFFSET -- it merely sees the cell above as a "0", then adds it to the value on the left. This gave me a value of -30 instead of 90 (120 - 30) in cell B5, and a value of 50 instead of 140 (90 + 50) in cell B6. Is this because the SUBTOTAL function ignores other subtotals? If so, then the only way I can have these individual sums done is by using the SUM function?

Thanks!
Byron

[Pete_UK]

SUBTOTAL does ignore other subtotal formulae within the range.

Hope this helps.

Pete

[bgo]

Thanks for the confirmation, Pete.

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.