Excel help please [Archive]

Jan

10 Jan 08, 01:49 PM

I will try to explain this as best I can.

I have two columns of figures - one for money in and one for money out. The totals of the two columns need to be the same. We will need more money to come in to make the columns balance but this amount needs to be calculated. How do I calculate the figure needed to go into the blank cell above the "total" without causing a circular reference? I'm sure I have done this before and the words "paste special" seem to ring a bell.

I do hope you know what I mean. :confused2

diby5

10 Jan 08, 02:05 PM

I will try to explain this as best I can.

I have two columns of figures - one for money in and one for money out. The totals of the two columns need to be the same. We will need more money to come in to make the columns balance but this amount needs to be calculated. How do I calculate the figure needed to go into the blank cell above the "total" without causing a circular reference? I'm sure I have done this before and the words "paste special" seem to ring a bell.

I do hope you know what I mean. :confused2
Give this a go:
Sum the first column
In the "blank" cell that you want the figure to go into enter an equation which subtract the total of the rest of that column from the total of the first column.

i.e. If the first column total is in cell A15 and the figures for the second column are from B1 to 13 then the equation in would be B14 would be =A15-(SUM(B1:B13))

then if you were to have column B totalled in cell B15 the two figures should be the same

This doesn't even seem that clear to me any more so if you want an example PM me your e-mail address and I'll send you the example detailed above

HTH

David

Armanaleg

10 Jan 08, 02:18 PM

This works for me David - I was trying to do a similar exercise the other day and couldn't get it to work.

Thanks :thumbs-up

diby5

10 Jan 08, 02:20 PM

Few, I kept re-reading it and thinking it made now sense at all. Glad it worked

David

Jan

10 Jan 08, 02:36 PM

Hey - it works. Thanks so much. That's a great help.

Andre

11 Jan 08, 08:06 AM

I have two columns of figures - one for money in and one for money out. The totals of the two columns need to be the same.:confused2

That's always the trick isn't it? Never seems to work in real life though. :)

Glad you got the spreadsheet sorted out though, I'd have done it the same way that diby5 suggested too.

Andre