calculating costs based on months and also minimums based on square footage

[houstontaff]

Ok so hello all

here is my problem. I have a report that i generate and i need some help and advice as it comes in various stages

firstly i need to work out the square feet from the inches i have which i believe i have in that i multiply length by width and divide by 144. done. This will give me the square footage.
The next part is the tricky part for me. I have a date that we received the freight into our facility. the client gets 60 dyas free storage and we calculate the storage on a quarterly monthly fee. so if they have the freight here for 3 months all they will be charged is for 1 month storage based on 60 free days.

The next issue is that if the storage fee is we have a minimum fee of $15 per month. so if something is less than $15 then i need it to read $15

How can i set something up that gives me one formulae to cover all sides of the deal from start to end instead of patch working the formulas and creating more work than necessary


[TABLE="width: 500"]
<TBODY>[TR]
[TD]Length (inches)[/TD]
[TD]width (inches)[/TD]
[TD]cost per sqr ft[/TD]
[TD]date received[/TD]
[/TR]
[TR]
[TD]1014[/TD]
[TD]144[/TD]
[TD]0.32[/TD]
[TD]04/04/2013[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]26[/TD]
[TD]0.65[/TD]
[TD]10/20/2015[/TD]
[/TR]
</TBODY>[/TABLE]


any help would be much appreciated

 

[NeonRedSharpie]

Is there a "last billed date"? For example, if you had a bill going out on 12/31 for Q4, would Row 2 be receiving a $15 bill or would it roll over into Q1-2016?

 

[houstontaff]

Is there a "last billed date"? For example, if you had a bill going out on 12/31 for Q4, would Row 2 be receiving a $15 bill or would it roll over into Q1-2016?

The last billed date would be the end of Q4

 

[NeonRedSharpie]

The last billed date would be the end of Q4

Ok....so assuming he gets billed the whole $15 for his 9 days, then it would be:

=IF(F2>90,MAX(15,E2*3),MAX(15,E2))

I think you'll need more columns of data or information to make it clean.

Basically this just says, "If they've been here more than 90 days, charge them for the full 3 months. Otherwise, charge them for just one month."

 

[houstontaff]

Ok so i think this solves part of my problem. However the $15 is a minimum fee. If say for example, the $15 minimum is exceeded by the multiplying of the sqr ft and the months, then i would like this to be entered into the cell rather than the $15

So the example would be that if the large piece of freight (first row) stays on hand for 3 months and the sqr ft is 1014 and it is 0.62 per month per sqr ft then this would reflect in the formula somehow

 

[NeonRedSharpie]

Ok so i think this solves part of my problem. However the $15 is a minimum fee. If say for example, the $15 minimum is exceeded by the multiplying of the sqr ft and the months, then i would like this to be entered into the cell rather than the $15

So the example would be that if the large piece of freight (first row) stays on hand for 3 months and the sqr ft is 1014 and it is 0.62 per month per sqr ft then this would reflect in the formula somehow

You're right, I used a helper column in E and F. My bad.

=IF(TODAY()-D2>90,MAX(15,A2*B2/144*C2*3),MAX(15,A2*B2/144*C2))

Where A is Length.