General discussion

Locked

Excel formula help

By Borg7of9 ·
I want to make a spreadsheet that keeps a bla as follows

Ref Amount Bal
5000
101 500 5500
102 400 5900
103 -200 5700

so far so good, but then I want to divide the balance into say 5 sections, I could easily set up 2 fields per section, one for amount, one for balance, but to keep form shorter, I want only one field for the updated balance. Iexample below


Ref Amount Bal Cat Bal A Bal B
5000 2000 3000
101 500 5500 a 2500 3000
102 400 5900 b 2500 3400
103 -200 5700 a 2300 3400

So I need to know how to make a formula that if Cat = A add balance to Bal A, If Cat = B add balance to Bal B etc.....

Thanks!!! I am very rusty on formulas!!!

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by SimY In reply to Excel formula help

You could use the following to calculate the values for Bal A and Bal B:

Bal A: =IF(Cat="a",OldBalA+Amount,OldBalA)
Bal B: =IF(Cat="b",OldBalB+Amount,OldBalB)

OldBalA is the value of Bal A from the previous row e.g. in the row where ref = 101, OldBalA = 2000.

Hope this helps

Collapse -

by Borg7of9 In reply to

Poster rated this answer.

Collapse -

by Borg7of9 In reply to Excel formula help

Thanks for the help, surprised you understand, as the examples I posted didnt align up correctly!!!

Anyway, I had to use this to apply may sunb balances, and this way I saved a lot of colums on the worksheet!!

Thanks again!

Collapse -

by Borg7of9 In reply to Excel formula help

Thanks for the help, surprised you understand, as the examples I posted didnt align up correctly!!!

Anyway, I had to use this to apply may sub balances, and this way I saved a lot of colums on the worksheet!!

Thanks again!

Collapse -

by Borg7of9 In reply to Excel formula help

Point value changed by question poster.

Collapse -

by Borg7of9 In reply to Excel formula help

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums