I need Excel help (again!)

  Forum Editor 11:09 17 Nov 2007

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 2007

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 2007

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

  Forum Editor 11:41 17 Nov 2007

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 2007

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 2007

Column E formula


copy downwards for each cell in column E

Column F formula


copy downwards for each cell in column E

End of rows Column E formula

End of rows Column F formula


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

  VoG II 11:55 17 Nov 2007

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 2007

Many thanks - I'll try your suggestion.

  Forum Editor 12:02 17 Nov 2007

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 2007

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 2007

It is on its way FE.

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

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

Microsoft Surface Book 2 hands-on review – bigger and 5x faster

Best kids apps for iPhone & iPad

Que faire si son iPhone ou iPad est tombé dans de l'eau ?