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

houstontaff

New Member
Joined
Dec 28, 2015
Messages
4
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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?
 
Upvote 0
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:

Code:
=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."
 
Upvote 0
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
 
Upvote 0
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.

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

Where A is Length.
 
Upvote 0

Forum statistics

Threads
1,223,083
Messages
6,170,035
Members
452,296
Latest member
chueeee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top