Straight Table Puzzle

[#1]

Hi

Im having difficulty doing something simple with my straight table. I trying to have some balances brought down to the next row and shown in different columns

I have the following dataset:

Day	In Pocket	Out Of Pocket
1	10	5
2	10	11
3	8	18
4	12	11
5	13	10
6	9	20
7	5	6

My end result will have to look like this:

Day	Balance	In Pocket	Out Of Pocket	Available	To Be Paid
1	0	10	5	5	0
2	5	10	11	4	0
3	4	8	18	0	6
4	0	12	11	1	0
5	1	13	10	4	0
6	4	9	20	0	7
7	0	5	6	0	1

On the first day the balance will always start with 0 then the Available and To Be Paid is a simple subtraction of "In Pocket - Out Of Pocket" (if its positive the amount goes to Available and brought forward to the Balance of the next row, if its negative its value is shown in the To Be Paid column).

Any help?

[petter · Accepted solution]

Balance:          If( RowNo(TOTAL) = 1 , 0 , Above( Available) )

In Pocket:       [In Pocket]

Out of Pocket:  [Out of Pocket]

Available:         If( Balance + [In Pocket] - [Out Of Pocket] < 0 , 0 , Balance + [In Pocket] - [Out Of Pocket] )

To Be Paid:      -If( Balance + [In Pocket] - [Out Of Pocket] < 0 , Balance + [In Pocket] - [Out Of Pocket] , 0 )

See also attached QVW...

[kaushiknsolanki]

Hi,

Here is the solution.

Regards,

Kaushik Solanki

[#4]

Brilliant thank you very much! I missed the first formula in my calcs

[#5]

Script alternative also correct thank you very much!