Google Spreadsheet Complex Query Formula

Question:

Hi G Community,

I was wondering how to create a formula to subtract one cell from the first populated cell above it in the same column, and put the results in the first cell below it. I attached a print screen of my example.

So as you can see the initial balance of Food is $100 (B2).

- On 6/2/13, I bought $25 (B7) of food. So the first populated cell above this cell is B2 with $100. So I need a formula in B8 that will subtract whatever I put in B7 from the first populated cell above it, which in this case it's B2.

- On 6/13/13, I bought $10 (cell B11) of food. So in this case, the first populated cell above B11 is B8.

So the first populated cell above a cell will always vary. Can anyone help me on how to create a formula like that?

Thanks in advance.

~Diana

Solution:

Put the following formula in Cell B8:

=query(arrayformula(indirect("B" & (row()-2) & ":B1"));"select Col1 where Col1>0 limit 1 offset " & (rows(query(arrayformula(indirect("B" & (row()-2) & ":B1"));"select Col1 where Col1>0";0))-1);0)-B7

Have a look at the screenshot below:

And now whenever you want to have the formula, you can just copy (ctrl C) from Cell B8 and then paste it (ctrl V)

For example paste it in Cell B12, so you will get the result $65 there. 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

Kishan.

iGoogleDrive