Formula help

I already use a formulain D2 that reads:
=MAX(0,C2-6)*17+MAX(0,C2-10)*33+MAX(0,C2-30)*50
This formula gives me the Amount to Charge(D2) for Days used at Origin(C2)
The Charges used in this formula are as follows:
The first 6 days are free
the 7th 8th 9th and 10th days are charged $17 a day
the 11th through the 29th day are charged $50 a day
the 30th day and above are charged $100 a day
FYI---->(17+33+50=100)
I need a formula that gives me the Days used at Destination.The problem Im
having is that I can't make the above formula work because the origin days
figure in and if the origin has used more than 6 days the 1st ten days at
destination are at $17(the rest of the charges are the same). eg: if the Days
used at Origin(C2) is 7, the Days used at Destination(K2) is 7. the Origin
charges should be for 1 day (17) and for 7 days at 17 (119) at Destination.
I need a formula to get the 119.

Any help comes Highly Appreciated!!!!!
I'll be checking on this post alot. If more info is needed please ask.

Thank you
Bryan J Bloom

"Bryan J Bloom" wrote:
> I already use a formulain D2 that reads:
> =MAX(0,C2-6)*17+MAX(0,C2-10)*33+MAX(0,C2-30)*50
> This formula gives me the Amount to Charge(D2) for Days used at Origin(C2)
> The Charges used in this formula are as follows:
> The first 6 days are free
> the 7th 8th 9th and 10th days are charged $17 a day
> the 11th through the 29th day are charged $50 a day
> the 30th day and above are charged $100 a day
[....]
> I need a formula that gives me the Days used at Destination.

I think you mean "that is based on", not that "gives me".
From you explanation below, K2 contains Days Used At
Destination. In any case, no formula can "give you" Days
Used At Destination, unless you have a cell with Total Days
Used as well as Days Used At Origin (C2).

> if the origin has used more than 6 days the 1st ten days at
> destination are at $17(the rest of the charges are the same).

Not sure I am interpreting you correctly. What if Days
Used At Origin is less than 6 days: is there any charge
for Days Uses At Destination; if so, what is the rule?

> eg: if the Days used at Origin(C2) is 7, the Days used
> at Destination(K2) is 7. the Origin charges should be
> for 1 day (17) and for 7 days at 17 (119) at Destination.
> I need a formula to get the 119.

Depending on your answer to my question above, I think
one of the following (untested) should solve your problem
or at least serve as a model for the solution.

=IF(C2 <= 6, 0,
MIN(10,K2)*17+MAX(0,K2-10)*33+MAX(0,K2-30)*50)

Eliminate "IF(C2 <= 6, 0," and the last parenthesis if you
always want to charge for destination usage.

Some of your description isn't really clear, but I think I know what you're
getting at. Try this:

=IF(C2>=6,E2*17+MAX(0,K2-10)*33+MAX(0,K2-30)*50,MAX(0,K2-6+C2)*17+MAX(0,K2-10)*33+MAX(0,K2-30)*50)

Also, you might need to adjust the amount of 30 in your formula to 29 to
reflect the pricing scale you mentioned. Otherwise $100 won't be charged
until day 31.

If that isn't what you're looking for, then perhaps repost with some more
clarification.

HTH,
Elkar

"Bryan J Bloom" wrote:

> I already use a formulain D2 that reads:
> =MAX(0,C2-6)*17+MAX(0,C2-10)*33+MAX(0,C2-30)*50
> This formula gives me the Amount to Charge(D2) for Days used at Origin(C2)
> The Charges used in this formula are as follows:
> The first 6 days are free
> the 7th 8th 9th and 10th days are charged $17 a day
> the 11th through the 29th day are charged $50 a day
> the 30th day and above are charged $100 a day
> FYI---->(17+33+50=100)
> I need a formula that gives me the Days used at Destination.The problem Im
> having is that I can't make the above formula work because the origin days
> figure in and if the origin has used more than 6 days the 1st ten days at
> destination are at $17(the rest of the charges are the same). eg: if the Days
> used at Origin(C2) is 7, the Days used at Destination(K2) is 7. the Origin
> charges should be for 1 day (17) and for 7 days at 17 (119) at Destination.
> I need a formula to get the 119.
>
> Any help comes Highly Appreciated!!!!!
> I'll be checking on this post alot. If more info is needed please ask.
>
> Thank you
> Bryan J Bloom
>

Errata ....

I wrote:
> =IF(C2 <= 6, 0,
> MIN(10,K2)*17+MAX(0,K2-10)*33+MAX(0,K2-30)*50)

That should be simply:

=IF(C2 <= 6, 0, K2*17+MAX(0,K2-10)*33+MAX(0,K2-30)*50)