Hi! I'm Tamara and I do analysis and am having trouble figuring out a formula for my sheet. I take hourly appointments, but some are multi-hour. I offer 2 services (different rates) and give discounts for multi-hour and will give rates below. I basically would love a formula where I can type in the type of appointment and number of hours, and it will automatically put in the correct total.

Type 1--> $300/hr...$200 each hour after
Type 2--> $200/hr...$150 each hour after
(half hours after is a plus but not necessary)

Thanks in advance!

---

Richard Answers:

I would use a little VLOOKUP table for this. First, here's a tutorial on VLOOKUP you should watch:

http://www.599cd.com/tips/excel/vlookup

Now what I would do in your case is set up a lookup table that contains all of your different rates. I'll set one up in cells H3:J5 that looks like this:

Type Rate1 Rate2
A 300 200
B 200 150

Now for your regular entries, I would have the Type in column A, followed by number of hours in col B. In col C I would put the Rate1 which represents the amount paid for the first hour. col D would be Rate2. Then you can add them up in col E for a total.

My test data looks like this:

Type Hours Rate1 Rate2 Total
A 1 300 0 300
A 2 300 200 500
A 3 300 400 700
A 4 300 600 900
B 1 200 0 200
B 2 200 150 350
B 3 200 300 500

So if those numbers check out, then here are the formulas I used:

C2: =VLOOKUP(A2,$H$4:$J$5,2,FALSE)
D2: =VLOOKUP(A2,$H$4:$J$5,3,FALSE)*(B2-1)
E2: =C2+D2

Of course, this assume that you are charging for a MINIMUM of one hour. Any half-hour appointments (or less) won't work.

I hope this helps. And remember, watch that video above for a better understanding of how VLOOKUP works. You can add as many rate types as you want to the lookup table, just make sure to expand your range in the formulas.