# Working out a pro rata rate rather than previous lump sum

1. ## Working out a pro rata rate rather than previous lump sum

Hi there,

In need of help. I am only a basic Excel user and formulas really aren't something I can get to grips with.

I have been asked to work out a total figure I used to charge in a lump sum, but now pro rata, which should run from January - December. As I am only starting this now, I have to work everything out from March - December.

I have a 600 row long spreadsheet which has a list of purchases and the amount owned per year.

Qty Maint. £ Due
2000 £3,000.00 March '14
1000 £1,500.00 Oct '14
500 £750.00 Dec '13
500 £750.00 April '14
500 £750.00 May '14

I need to work out the above pro rata. I can add any columns if required but I just do not know how to work this out on excel.

As the first entry in the above tables shows March, which is due next week, I know that I need to work out 10 12ths of £3000, which would be £2500.
Come October, I need to work out 3 12ths..

How can I get Excel to work this out per row?

Thanks so much.
Tim

2. ## Re: Working out a pro rata rate rather than previous lump sum

If your "dates" are in teh saqme format as you copied (ie, text, not real dates), then try this, copied down....
=B2/12*(12-MONTH(DATEVALUE(1&" "&LEFT(C2,FIND("'",C2,1)-2)&" "&RIGHT(C2,2)))+1)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1