+ Reply to Thread
Results 1 to 2 of 2

Working out a pro rata rate rather than previous lump sum

  1. #1
    Registered User
    Join Date
    02-24-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    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. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Working out deeming rate of interest
    By coach.32 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-11-2013, 02:29 AM
  2. Replies: 2
    Last Post: 01-16-2012, 08:14 PM
  3. Working out Various Hourly Rate : TImesheet
    By fgbuk in forum Excel General
    Replies: 2
    Last Post: 07-23-2011, 03:51 PM
  4. Previous working days date (Mon-Fri working week).
    By intothewild in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-22-2011, 01:00 PM
  5. Working out an interest rate
    By [OutCast] in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-18-2009, 07:00 PM

Bookmarks

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