Excel Jululian

[George A. Jululian]

Hi
I need your help on this formula
I need to calculate Storage Fees
Column (A) Date In
Column (B) Date out
Column (C) numbers of days in Stock (A1-B1)=112days
The First ten days free
The fees on First 20 days after the 10days = 1 dollar
The fees on second 20 days after the 10days and 20 day = 2 dollar
The fees on Third 20 days after the 10days,20day and 20day = 3 dollar
The fees on remaining days after the 10days, 20day, 20day, and 20 day = 10
dollar


Date In Date out Numbers of days

05/11/2008 25/02/2009 112


Regards

 

[Stefi]

=MAX(0,C2-10)*1+MAX(0,C2-30)*2+MAX(0,C2-50)*3+MAX(0,C2-70)*10

Regards,
Stefi

„George A. Jululian†ezt írta:

 

[George A. Jululian]

Hi
I made the calulation and result is 540 and not as formaul 872

10 X 0 = 0
20 X 1 = 20
20 X 2 = 40
20 X 3 = 60
42 X 10 = 420

Total days 112 Total amount 540

 

[Stefi]

Sorry, I didn't test it carefully. This is the right version
=MAX(0,MIN(20,$C$2-10))+MAX(0,MIN(20,$C$2-30))*2+MAX(0,MIN(20,$C$2-50))*3+MAX(0,C2-70)*10

Regards,
Stefi

„George A. Jululian†ezt írta:

 

[David Biddulph]

Yes, I think Stefi missed a bit out of her formula, so she's not limiting
the 1 dollar range to 20 day, but continuing it in parallel with the 2
dollar range, and the higher ranges.

You could change the
=MAX(0,C2-10)*1+MAX(0,C2-30)*2+MAX(0,C2-50)*3+MAX(0,C2-70)*10
to
=MAX(0,C2-10)*1+MAX(0,C2-30)*(2-1)+MAX(0,C2-50)*(3-2)+MAX(0,C2-70)*(10-3) or
=MAX(0,C2-10)*1+MAX(0,C2-30)*1+MAX(0,C2-50)*1+MAX(0,C2-70)*7 or
=MAX(0,C2-10)+MAX(0,C2-30)+MAX(0,C2-50)+MAX(0,C2-70)*7

Another way of changing's Stefi's formula would be
=MIN(20,MAX(0,C2-10))*1+MIN(20,MAX(0,C2-30))*2+MIN(20,MAX(0,C2-50))*3+MAX(0,C2-70)*10
or
=MIN(20,MAX(0,C2-10))+MIN(20,MAX(0,C2-30))*2+MIN(20,MAX(0,C2-50))*3+MAX(0,C2-70)*10

 

[George A. Jululian]

Many Thanks on your great support

Yes, I think Stefi missed a bit out of her formula, so she's not limiting
the 1 dollar range to 20 day, but continuing it in parallel with the 2
dollar range, and the higher ranges.

You could change the
=MAX(0,C2-10)*1+MAX(0,C2-30)*2+MAX(0,C2-50)*3+MAX(0,C2-70)*10
to
=MAX(0,C2-10)*1+MAX(0,C2-30)*(2-1)+MAX(0,C2-50)*(3-2)+MAX(0,C2-70)*(10-3) or
=MAX(0,C2-10)*1+MAX(0,C2-30)*1+MAX(0,C2-50)*1+MAX(0,C2-70)*7 or
=MAX(0,C2-10)+MAX(0,C2-30)+MAX(0,C2-50)+MAX(0,C2-70)*7

Another way of changing's Stefi's formula would be
=MIN(20,MAX(0,C2-10))*1+MIN(20,MAX(0,C2-30))*2+MIN(20,MAX(0,C2-50))*3+MAX(0,C2-70)*10
or
=MIN(20,MAX(0,C2-10))+MIN(20,MAX(0,C2-30))*2+MIN(20,MAX(0,C2-50))*3+MAX(0,C2-70)*10

 

[George A. Jululian]

Many Thanks on your great support

Sorry, I didn't test it carefully. This is the right version:
=MAX(0,MIN(20,$C$2-10))+MAX(0,MIN(20,$C$2-30))*2+MAX(0,MIN(20,$C$2-50))*3+MAX(0,C2-70)*10

Regards,
Stefi

„George A. Jululian†ezt írta:

 

[George A. Jululian]

Many thanks on great support

Sorry, I didn't test it carefully. This is the right version:
=MAX(0,MIN(20,$C$2-10))+MAX(0,MIN(20,$C$2-30))*2+MAX(0,MIN(20,$C$2-50))*3+MAX(0,C2-70)*10

Regards,
Stefi

„George A. Jululian†ezt írta:

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„George A. Jululian†ezt írta: