Excel Calculations

  Taurus 20:24 14 Mar 2008
Locked

I have set up a form in Excel (2007) that requires simple addition of columns of figures representing cash. Although under normal circumstances this would be simplicity itself, it is made more difficult by the nature of the form, it is very desriptive and requires that the £s and ps be put in seperate columns. I'm sure that there must be a way to do the calculation but I have not come across this before and I'm having a problem, can anyone help please?

Dave

  VoG II 20:27 14 Mar 2008

What sort of Form - a UserForm?

  Taurus 20:34 14 Mar 2008

Hi VoG™, no, I am being a bit grand by saying it's a form it's just a simple spreadsheet below the pretty layout, just to present an end of year balance.

  VoG II 21:02 14 Mar 2008

Say you have pounds in A2:A10 and pence in B2:B10 then the following formula will give you the sum

=SUM(A2:A10)+SUM(B2:B10)/10

  VoG II 21:04 14 Mar 2008

Should of course be

=SUM(A2:A10)+SUM(B2:B10)/100

  Taurus 21:14 14 Mar 2008

Thanks for that VoG™, I can see that that formular would work but it would put the total in one cell, I need the pounds and pence to be in separate cells below thier respective columns.

  Taurus 21:15 14 Mar 2008

Opps, I mean formula!!

  Taurus 21:17 14 Mar 2008

Oops 2, I mean 'their'. It seems red wine and thinking don't marry well in my head.

  2bathred 21:18 14 Mar 2008

just sum each column then. You wont have the full total tho' unless you have another cell for that

  VoG II 21:21 14 Mar 2008

Try this

Pounds =SUM(A2:A10)+INT(SUM(B2:B10)/100)

Pence =MOD(SUM(B2:B10),100)

  Taurus 21:28 14 Mar 2008

If I sum each column I would end up with something like £365 in one column and 2,395pence in the other.The pence column needs to be divided by 100 and the whole numbers added to the £ column and the remainder remaining in the pence column; this is what is causing the problem.

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

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?