Excel Calculations

  Taurus 20:24 14 Mar 08

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?


  VoG II 20:27 14 Mar 08

What sort of Form - a UserForm?

  Taurus 20:34 14 Mar 08

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 08

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


  VoG II 21:04 14 Mar 08

Should of course be


  Taurus 21:14 14 Mar 08

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 08

Opps, I mean formula!!

  Taurus 21:17 14 Mar 08

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

  2bathred 21:18 14 Mar 08

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 08

Try this

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

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

  Taurus 21:28 14 Mar 08

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.

Huawei P10 review

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

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now