I've asked for help with Excel formulae before, and I'm asking again, I'm afraid.
I have to start using a spreadsheet that does this:-
column 1 - text column 2 - text column 3 - additions to contract (currency) column 4 - credits to contract (currency) column 5 - payments received (currency) column 6 - this column will have an opening contract balance which should update automatically to reflect changes made in columns 3/4/5
Finally there should be a column 7 that expresses the total of the payments received in column 5 as a percentage of the changing balance in column 6
Not sure what you mean re: column 6, so I may be over simplifying, but for column 7 isn't it simply format column 7 as Percentage, and then =E2/F2 (column5/column6) for each row in column 7. That seems altogether too simple, I must be missing something.
Assuming that the opening balance is on row 2 and entries commence on row 3 then I think that column 7 should have the following formula =SUM(E$3:E3)/F2 formulated as a percentage. The next row down will have =SUM(E$3:E4)/F3 and so on.
will contain text, as will those in column 2 and the text entries will all be different.
Column 3 cells will contain currency totals that must be added to a running balance in column 6. The opening entry in column 6 will contain a contract total, and that needs to constantly update as amounts are entered in the other columns.
Currency entries in columns 4 and 5 must be subtracted from the running balance.
The updated total needs to appear (in column 6)in the same row as entries in the other columns. I'll therefore end up with a lot of additions and subtractions in columns 3,4 and 5, all of which will be reflected in a changing contract total in the cells in column 6.
Just to clarify, as I think I'm being thick. Columns 1 and 2 are irrelevent with regards to formulas, as they are effectively just text notes of some sort?
Column 3 values you want to be summed together as a running total in column 6, with column 6 having also subtracted the values in columns 4 and 5 for that row to give a running total of all of col 3 minus the sum of col 4 and 5 for that specific row only.
What I still don't get, is the opening value for col 6, do you mean you want something different in the opening entry? Or are all the cells in col 6 doing the same thing?