cell formula to get storage rate

[steve400243]

Hello, I am having trouble putting this formula together, and any help would be much appreciated.

Storage: 7 free calendar days including devan date > $3.50 per cbm, or $50 min charge for next 5 days,
12th day - additional $3.50 per cbm per day - no min

Cell H4 is a completed date
H5 is the effective storage date, 7 calendar days later - including the completed date
F13 thru F35 would be the CBM
K13 thru K35 is the picked up date

Here is what I have so far, but it is not working correctly.

=IF(F13<0.01,0,(MAX(((K13-$H$6)*F13*3.5)+IF(K13-$H$4<7,0,MAX(((K13-$H$5)*3.5),50))

Not sure if there would be a better way to get my desired results.

 

[Nan3_mS]

I am not sure if I get it right... Each line starting from the 13th represents a different volume to be stored but all of them have the same effective storage date, which would be H5, right?

 

[steve400243]

I am not sure if I get it right... Each line starting from the 13th represents a different volume to be stored but all of them have the same effective storage date, which would be H5, right?

Hello, Thanks for looking -

Yes Cells F13 thru 35 would be different Volume in CBM
K13 Thru K35 is the picked up date.
H5 is 7 days after the date in H4, and H6 is the 12th day after H4.

 

[Nan3_mS]

Hello, Thanks for looking -

Yes Cells F13 thru 35 would be different Volume in CBM
K13 Thru K35 is the picked up date.
H5 is 7 days after the date in H4, and H6 is the 12th day after H4.

Ok... I would try this:

=IF(K13-H4<7;0;IF(K13-H4<=12;MAX(3.5*(K13-H4)*F13;50);MAX(3.5*(K13-H4)*F13;50)+3.5*F13*(K13-H4-12)))

And here I am considering that after the 12th day the "initial fee" (8th to 12th day) freezes and you start charging $7 ($3,5*2) per CBM per aditional day... I hope I got it right. It's my first reply here

 

[steve400243]

Nan3, Getting an error with this formula - "When the first character is an equal(=) or minus (-) sign, excel thinks it is a formula" ?

 

[steve400243]

After playing around with it a bit, I got this to work, but if the CBM is higher and the charge is more than the min of $50.00 it will not figure it in.

=IF(F13<0.01,0,ROUND(MAX(((K13-$H$6)*F13*3.5),0)+IF(K13-$H$4<7,0,MAX(((K13-$H$5)*3.5),50)),2))

 

[Nan3_mS]

Getting an error with this formula

Oh ok... maybe it's because I use ";" instead of "," in my formulas... idk, it's working here
_
About your formula, I am glad somehow you figured it out.
I believe you're missing the parenthesis on the second IF, though...

=IF([B]([/B]K13-$H$4[B])[/B]<7...

 

[steve400243]

Thanks for looking at it Nan3 -