Calculation time and charge rates

I am trying to figure out a formula to figure out how much to charge for hrs of a rental. There is an automatic $1000 charge regardless of time used. the rate chart is as follows:

$1000 + hourly charge = total

=<50 hrs= $15/hr
>50hrs but <100hrs= $6.50/hr
>100hrs=$4.10/hr

ex: so is A1= 200hrs then i want B1 to equal $1820.($1000 + (200 x 4.1))=1820.

I have an idea on how to write the formula, but i am having a little bit of trouble with it.

thank you so much!!!!!!

ok i misunderstood what i was being asked to do. there is a change in the charging. okay if they work 200 hrs, they will be charged $2485. here is the breakdown:
$1000
50hrs @ $15/hr
50hrs @ $6.50/hr
100hrs @ $4.10/hr

so how can i adjust the formula to use this method?

.15hrs to 50hrs= $15/hr
50.15hrs to 100hrs= $6.50/hr
100.15+ = $4.10/hr

That's an odd schedule:

50 hours costs $1750.00
51 hours costs $1331.50
99 hours costs $1643.50
100 hours costs $1410.00

Strong incentive to sit around for an extra hour ...

Should the changed rates not apply to the incremental time, i.e.,

=1000 + SUMPRODUCT( (A2 > {0,50,100}) * (A2 - {0,50,100}) * {15,-8.5,-2.4})

Compared:

Please Login or Register  to view this content.

1000+750+325+410= 2485

That's exactly what the formula I suggested returns:

=1000 + SUMPRODUCT( (A2 > {0,50,100}) * (A2 - {0,50,100}) * {15,-8.5,-2.4})

sorry!! the example they gave me is different. i think they are wrong. let me talk to them again to see exactly what the heck they are talking about.

BTW, there cannot be 'null spots' in the data, e.g., <= 50 hours pays one rate, >= 50.15 hours pays another, because there's no definition of what happens in between.

The hours could be rounded to the nearest 15 minutes, or rounded up to the next 15 minutes. Rounding to 0.15 hours (9 minutes) doesn't make much sense.

ok here is the lastest and greatest oh what this messed up rate system is suppose to be:

$1000 + hourly charge
hrs: charge:
0-50 free
50.01-100 $15/hr
100.01-200 $6.5/hr
200.01+ $4.1/hr


ex: customer 1 has rental for 502.9hrs
$1000 min charge
$ 0 first 50hrs (hr 0-50)
$ 750 2nd 50hrs (hr 50.01-100)
$ 650 1st 100hrs (hr 100.01-200)
$1241.89 for 302.9hrs (hr 200.01-502.9)
$3641.89 Total

hopefully this helps.

sorry about the .15 i meant .25 but it doesn't matter.

=1000 + SUMPRODUCT( (A2 > {0,50,100,200}) * (A2 - {0,50,100,200}) * {0,15,-8.5,-2.4})

Please Login or Register  to view this content.

thanks you so much!!! that seems to be it. i will just have to figure out how to change it if any of the time charges change or the rate changes. thanks again.

that is awesome! thanks again!!!!

okay I am an idiot. if i wanted to take off the first 50 hrs for free and charge $20/hr for them, which array would i change? i suck at this.

i think it would be this

=1000 + SUMPRODUCT( (A2 > {0,50,100,200}) * (A2 - {0,50,100,200}) * {20,-5,-8.5,-2.4})

right?

can i change a fix number (20,-5,-8.5,-2.4) to a value entered into a certain cell (g4,g5,g6, g7)? that way i can just change the value in the cell and not have to change the formula?

Create two named ranges, Breakpoints and RateDiffs (they must be the same size), and change the formula to

=1000 + SUMPRODUCT( (A2 > Breakpoints) * (A2 - Breakpoints) * RateDiffs)

???? again i am an idiot. are you saying to make 2 columns and name each column?

In some column, put this in four cells:

0
50
100
200

Select those four cells, and in the Names box (left of the fomula bar), type Breakpoints.

Someplace else, enter

20
-5
-8.5
-2.4

Select those four cells, and in the Names box (left of the fomula bar), type RateDiffs.

ok that works, but the question that is posed to me is can you just enter 20, 15, 6.5, 4.1 in the cell and it will use that rate and then you change it to 40, 20, 8,5 and it use that rate? i know you are probably getting tired of this, but i am trying to help out a friend. the main reason that he needs to be able to just punch in the # and not put in the first # and then do the differences, is that others may use this and mess up the formula if he is not there to explain it.

See attached.

thanks!!!!!

You're welcome