My Excel ignorance is showing again.

Forum Editor 00:39 05 Oct 2009
Locked

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

I hope that's understandable - can anyone help?

Technotiger 07:30 05 Oct 2009

Where's VoGâ„˘ when you need him ???

Well, gets it to the top of the pile :-)

Picklefactory 08:55 05 Oct 2009

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.

VoG II 09:51 05 Oct 2009

Assuming headings in row 1 and an opening balance in F2 then in F3

=F2+C3-D3-E3

(I think - I'm not sure which columns should be added or subtracted).

Copy F3 as far down as needed.

Woolwell 09:57 05 Oct 2009

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.

Abel 11:09 05 Oct 2009

FE if you give me permission to e-mail you, I'll send an example of your problem's solution.

Abel

Forum Editor 17:59 07 Oct 2009

for my tardiness in getting back to my thread - I've been neglectful, and I'm sorry. Thanks for your responses.

Remember that I'm brain-dead where Excel is concerned - ask me to work with Word or PowerPoint and I'll make them sing, but Excel.....different story.

Forum Editor 18:00 07 Oct 2009

That would be great - send to: moderator@idg.co.uk please.

Forum Editor 18:09 07 Oct 2009

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.

Picklefactory 10:03 08 Oct 2009

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?

Picklefactory 10:13 08 Oct 2009

If I'm getting it straight, then assuming you're working with column A-G and have titles in Row 1 then: -

In F2(opening cell for col6) copy/paste =C2-(D2+E2)
In F3 enter =SUM(\$C\$2:C3)-(D3+E3) and click/drag that down the column.

Column 7 (%), format that column as Percentage and in cell G2 enter =E2/F2 and click/drag that.

Hope I'm understanding the task correctly although I'm not sure I have.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman