Results 1 to 5 of 5

20160527, 16:51 #1
 Join Date
 Dec 2009
 Location
 Marseilles
 Posts
 67
 Thanks
 5
 Thanked 1 Time in 1 Post
Stuck on formula for stock management
I've pushed my limited grasp of Excel to the limits in trying to solve this one, maybe someone can help.
I need a formula to calculate the chares for stored pallets, given that the pallets are charged at a fixed rate (I've put $10 in this example) per 30day period, indivisible. If a pallet stays 31 days in storage, it's automatically charged for the next full 30day period.
Here is a screenshot showing a few representative rows of my Excel chart:
Storage2.JPG
My formula in col. L is =IF(K4<=30,H4*10,"C/F"). This just takes anything that went out within the first 30day period and multiplies the no of pallets by $10. Anything that runs over 30 days shows C/F.
I want to put a formula in col.M that will calculate the charge for the pallets going out in the second 30day period (3160), or show C/F if the pallets stay longer than 60 days, the calculation for these being handled in col. N. ( For the rows containing pallets that went out in the first 30day period, I just want the cells in M and N left blank.)
Likewise, col. N would do the same for pallets going out in the 6190 period.
I just can't get my head around it!
Thanks for any tipsLast edited by Marp; 20160527 at 17:02.

20160527, 16:59 #2
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,492
 Thanks
 33
 Thanked 63 Times in 59 Posts
The attachment cannot be displayed. It would be better if you could actually attach the Excel sheet or a sample of it.
In M, you might want: =if($K4<=30,"",IF(and($K4>30,$K4<=60),$H4*10, "C/F"))
In N, try: =IF($K4<=60,"",$H4*10)Last edited by kweaver; 20160527 at 17:01.

20160527, 17:03 #3
 Join Date
 Dec 2009
 Location
 Marseilles
 Posts
 67
 Thanks
 5
 Thanked 1 Time in 1 Post
Wow, that was quick! Sample attached, thanks!

20160527, 17:21 #4
 Join Date
 Dec 2009
 Location
 Marseilles
 Posts
 67
 Thanks
 5
 Thanked 1 Time in 1 Post
Worked perfectly!
I was having trouble getting the if/and to work after the first if but yours seems to be just fine.
Many thanks!

20160528, 09:25 #5
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,931
 Thanks
 6
 Thanked 9 Times in 7 Posts
Hi,
I'm attaching a workbook with a suggestion. Take a look.
Look at the columns that have row 1 shaded in yellow  that's what I added.
 col L just calculates the number of days for the palettes based on Movement Date and Date In (plus 1 based on your Col K). It seemed like your column K was based on this but why not let Excel do the calculations? This is not critical to the 2nd set of cols added  they use col L but the formulas could have used col K
 cols PR are equivalent to your MO. PR use 1 formula that is filled across the cols and down the rows. I wasn't sure if you wanted C/F throughout the cols if the col was, in effect, not applicable but that's the way I approached it for now. The formula could be changed if you want to leave a blank like kweaver's formulas but I interpreted your original post to show C/F everywhere except for where there is an actual charge.
The formula in P2 changes automatically as you fill across and down to change col and row references (unless there's a $ in front of the number or letter telling Excel not to change cols and rows), so let me explain just P2
=IF(ROUNDUP($L2/30,0)=RIGHT(P$1,2)/30, 10*$H2,"C/F")
The ROUNDUP($L2/30,0) takes the # days (either the original col K could be used or the calculated # days from col L), divides by 30 since your intervals are 30 days long, and rounds up to 0 decimal digits (giving a whole # of 30day intervals). So this calculates the # of 30day intervals for the row that the palettes were stored.
The RIGHT(P$1,2)/30 looks at the last 2 characters in Row 1 of the column and divides by 30 to also get the # of 30day intervals relevant for that column. There are other ways to do this if the column labels in row 1 had been done differently but this is a small point.
The formula compares the two 30day intervals and gives back C/F or the charge.
You might also want to consider putting the charge somewhere in a cell so you can change it in one place and the formulas in cols PR would just refer to that cell rather than having to update these formulas if the charge changes.
Let me know if you have any questions.
Fred