title: excel: sum of currency in two columns
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>Example:</p>
  
  <pre><code>currently
       | £ | p |
       | 5 | 20|
       | 4 | 90|
  Total| 9 |110|
  
  required
       | £ | p |
       | 5 | 20|
       | 4 | 90|
  Total| 10| 10|
  </code></pre>
  
  <p>Does anyone have any suggestions?</p>
  
  <hr>
  
  <p>Answer (StackOverflow wont let me answer for 8 hours...)</p>
  
  <p>Sorted it :)</p>
  
  <p>The £ column I've summed and added to the sum of the pence column divided by 100 rounded down to the nearest integer:</p>
  
  <pre><code>=SUM(POUND COLUMN)+ROUNDDOWN((SUM(PENCE COLUMN)/100),0)
  </code></pre>
  
  <p>and then in the pence total I've summed the pence column, divided by 100, and then found the remainder using the MOD function</p>
  
  <pre><code>=MOD(SUM(PENCE COLUMN),100)
  </code></pre>