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