Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Love "PRORATE"

Charles DESCOTES
Charles DESCOTES ✭
edited 12/09/19 in Archived 2015 Posts

The PRORATE function does not exist in Excel (to my knowledge, reinventing it was cumbersome).

We use it to spread budget accross time.

Indeed, with the right columns and rows, Yearly (can be quarterly, monthly or whatever) budgets adjust as the dates change.

Then dedicated boxes consolidate to overview at a glimpse.

PRORATE is straight forward to use with 5 logical parameters. See http://help.smartsheet.com/customer/portal/articles/775363-using-formulas#advanced

I find it especially useful when I make up a new project plan, with a lot of date changes at this stage => Budget is always aligned :-)

Would you like me to issue a short template?

Tags:
· Share on FacebookShare on Twitter

Comments

  • Jeremy Michels
    Jeremy Michels ✭✭✭✭✭

    I would love to see at template. What industry are you working in?

    · Share on FacebookShare on Twitter
  • Dean Krontz
    Dean Krontz ✭✭✭

    Charles,

    I was trying to find some time to do something just as you described. IF you have a template already created I would appreciate if you could share.

    Thanks in advance.

    · Share on FacebookShare on Twitter
  • Charles DESCOTES
    Charles DESCOTES ✭
    edited 04/07/15

    Ok, I did one quick example with PRORATE budgeting to make it clearer.

    Trevor helped me making it available to you (see bottom).

    You will also see how I check Start and Finish dates thanks to Harvey balls (I love this gimmick).

    For the latter and to verify the budget robustness, conditional formatting is the watchdog.

    Your comments/questions are welcome.

     

    Access the example:

    Any paid user who clicks the following distribution link, will have a copy of the sheet added to their account (and they will be the owner of the copy). If they are not a paid user, they can give this link to a paid user who can get the sheet and share it with them.

     
    · Share on FacebookShare on Twitter
  • Issa M
    Issa M ✭✭✭

    Hi Charles, thanks for posting this. This is extremely helpful. I'm having trouble figuring out how to adapt this for monthly rather than yearly allocation though. In particular, I'm not sure how monthly allocation is compatible with the date format in the formula. How would the formula change from year, month, day if year is eliminated? Can you share either what the formula should look like or a template you've created?

    Thanks for any help you can provide!

    Issa

    Screen Shot 2019-03-01 at 12.20.57 AM.png

    · Share on FacebookShare on Twitter
  • Issa M
    Issa M ✭✭✭

    Never mind. I figured it out. Pretty straightforward actually. I guess I just needed to sleep on it. Thanks anyway! 

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories