+ Reply to Thread
Results 1 to 6 of 6

Basic Calculator - In over my head, please help!

  1. #1
    Registered User
    Join Date
    11-14-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2003
    Posts
    2

    Question Basic Calculator - In over my head, please help!

    Hi There!

    So I'm trying to build a custom calculator for my company's website. I think I'm knowledgable enough to embed the calculator once I have it built in excel but that's where I'm stuck. I wish I had paid more attention in my college excel class.

    I'm trying to make a calculator where the inputs are two dates (MM/DD/YYYY). And the output is a number (Average Daily Rate).

    The two dates would be the start and end dates of a lodging reservation and the number represents the government reimbursement rate based upon those dates. The rate for each day is attached to this post.

    The Federal Government offers what's called a "sliding scale" per diem that works like this: Days 1-60 they get 100% per diem, 61-120 50% & 121+ they get 25%. The full rates also vary by month (see attachment). OR here is the GSA website: www.gsa.gov/portal/category/100120

    For Example:
    Joe Smith is on a temp detail in Washington DC for 95 days starting on November 27. How do we determine is average daily rate?

    Referencing the website we know he receives $183 per day for 4 days in November. $183 per day for 31 days in December. $183.00 for 25 days in January. $91.5 (183*0.5) for 5 days in January. $91.5 for 30 days in February.

    So some basic algebra is involved: ($183 * 60 + $91.5 * 35) / 95 = $149.28 = ADR


    Can someone please explain to me how to do this?? I'd really appreciate your help!
    Attached Files Attached Files

  2. #2
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    401

    Re: Basic Calculator - In over my head, please help!

    Maybe this will help.

    http://office.microsoft.com/en-us/ex...003056111.aspx
    -------------
    Tony

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: Basic Calculator - In over my head, please help!

    Doylejg3,

    See the attached sheet in my next post - I updated it so the formula is entirely in one cell. Seems to work, but you should test it to be sure!

    - Moo

    P.S. - 95 days from (and including) November 27, 2012 is March 1, 2013. Also, there are 31 days in January, and only 28/29 days in February, too. Your calculation should have included 1 day of March, which is why the ADR in my example is $149.51, instead of your example of $149.28

    -- ATTACHMENT REMOVED: ADDED UPDATED FILE IN NEXT POST --
    Last edited by Moo the Dog; 11-17-2012 at 03:20 AM. Reason: Updated file - Formula now in ONE cell !!!

  4. #4
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: Basic Calculator - In over my head, please help!

    OK, here is the updated file with the formula in one cell. Give it a test run and let me know.

    - Moo
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-14-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Basic Calculator - In over my head, please help!

    Moo,

    This seems to be working perfectly. Exactly what I was trying to do! Thanks so much for your help!!

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: Basic Calculator - In over my head, please help!

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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