I need Excel help (again!)

  Forum Editor 11:09 17 Nov 07
Locked

As regular members are aware, I have a relatively blsnk mind where Excel is concerned - it's not something I encounter much in my daily work.

From time to time I need help with something, and here's an example; simple for an experienced Excel user, but beyond my basic skill set, I'm afraid - I don't have that kind of mind.

This is a fee calculator, and is pretty basic. if anyone can provide me with a template, or point me to one I will be grateful:

5 columns, A-F. A is text, and all the others are GBP currency.

I want to make entries in B,C, and D, and have E automatically calculate a 30% percentage of the row total and enter it. I then want F to add the totals in B,C,D, and E and enter it in F for each row. In addition I would like E to have a constantly updating total of itself.

In addition, I want F to show a constantly updating grand total of its own totals in the cell below the last entry. I'll end up with dozens of rows, each one individually totalled in F, with a grand-total for the whole sheet at the bottom of F and a total for E alongside.

I hope my description makes sense. Thanks in advance for any help.

  shellship 11:14 17 Nov 07

Oh dear, I used to be able to do this in my sleep (when I was not reading Lotus 123 manuals to send me to sleep) but now it would take rather a long time. Much quicker to let VoG do it! Sorry!

  wee eddie 11:36 17 Nov 07

Perchance ~ Have you forgotten VAT or maybe that only goes on the Invoice?

  Forum Editor 11:41 17 Nov 07

VAT calculations are carried out by accounting software at the time the invoices are raised - this is an in-house fee predictor; VAT information isn't required at this stage, but thanks for raising the point.

  Forum Editor 11:43 17 Nov 07

I'm sure that VoG will smile benignly, and type my little formula with one hand while correcting an EXCEL manual with the other.

  Zak 11:47 17 Nov 07

Column E formula

=SUM(B2:D2)*30%

copy downwards for each cell in column E

Column F formula

=SUM(B2:E2)

copy downwards for each cell in column E


End of rows Column E formula
=SUM(E2:E21)

End of rows Column F formula

=SUM(F2:F21)

Any help FE, I could send you the spreadsheet, please advise how.

  VoG II 11:55 17 Nov 07

I have deviated from the instructions by placing the column totals at the top of the sheet. This means that you can keep adding rows without having to change the formulas for the totals. I have used an IF statement for the row totals so that if column B is empty you don't get a 0 in E and F.

Here is a picture of my spreadsheet showing the formulas used:

click here

  Forum Editor 11:59 17 Nov 07

Many thanks - I'll try your suggestion.

  Forum Editor 12:02 17 Nov 07

Just refreshed my screen to see your post. The If statement is a winner - as you rightly suspected, there will not be figures in all the columns for all the rows.

Having the totals at the top of the sheet is also invaluable.

Many thanks, as always.

  Forum Editor 12:10 17 Nov 07

Any chance you could send me that sheet? Someone the office says it will be quicker - we can't copy/paste your formulae from a jpeg.

  VoG II 12:17 17 Nov 07

It is on its way FE.

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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…