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!

Maybe this will help.

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

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 --

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

- Moo

Moo,

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

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.

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