Excel 2007 : 'Right' function for numbers

**Matt Parsons** · 10-11-2011, 10:47 AM

Good afternoon people,

I have a spreadsheet that works out payment instalments, with each payment worked out where the first instalment will usually be an odd amount and the rest calculated with standard amounts.

For instance, if a total payment of £226.64 is to be paid in instalments, the first payment would be £26.64 and the remaining 10 would be £20 each.

I have created a formula which works for odd amounts (such as the example above) but does not when the amount involves 0 change ie £220.00. The function I am using is the 'RIGHT' function ( =RIGHT(S23,4) ), which does not seem to work correctly for numbers where the decimal is .00.

Does anyone have a suggestion on how to capture the last four digits of a number correctly?

Cheers,
Matt

**romperstomper** · 10-11-2011, 10:54 AM

Is it always 11 payments?

**Marcol** · 10-11-2011, 10:57 AM

Try this

Please Login or Register  to view this content.

Format the cells "Currency".

Is that what you mean?

**Nikeyg** · 10-11-2011, 10:59 AM

If you format the cell to text and then type it then 220.00 will display correctly as 220.00 in the cell.
Plus, you'll need to change your right formula to =RIGHT(S23,5) as the dot is considered to be a character in the text.

**NBVC** · 10-11-2011, 11:01 AM

You can try converting to text first within a formula...

e.g.

=RIGHT(TEXT(S23,"#.00"),5)+0

formatted as necessary to show the decimals....

**Matt Parsons** · 10-11-2011, 11:17 AM

Wow, thanks! I didn't realise there were so many ways to accomplish this!

romperstomper: no it's not always 11 payments, it can be anything as the amount can be varied and the payment instalments can be varied also.

Marcol, Nikeyg & NVBC: Thanks, all of these methods work!

Cheers people!