excel: sum of currency in two columns

I've got an expenses form for my work which, to be fair, is terrible. I constantly have to fill them out and so I'm trying to add some formulae to speed up the process. I want to sum the total money which would be a simple =sum(). However my problem is that the money is split into two columns | £ | p | which means that I end up with the pence column not over flowing into the pounds when it hits 100. I cannot change the format of the form. Example: currently | £ | p | | 5 | 20| | 4 | 90| Total| 9 |110| required | £ | p | | 5 | 20| | 4 | 90| Total| 10| 10| Does anyone have any suggestions? --------------------- Answer (StackOverflow wont let me answer for 8 hours...) Sorted it :) The £ column I've summed and added to the sum of the pence column divided by 100 rounded down to the nearest integer: =SUM(POUND COLUMN)+ROUNDDOWN((SUM(PENCE COLUMN)/100),0) and then in the pence total I've summed the pence column, divided by 100, and then found the remainder using the MOD function =MOD(SUM(PENCE COLUMN),100)

以上就是excel: sum of currency in two columns的详细内容,更多请关注web前端其它相关文章!

赞(0) 打赏
未经允许不得转载:web前端首页 » excel

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

前端开发相关广告投放 更专业 更精准