# Thread: Stuck on formula for stock management

1. ## 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 30-day period, indivisible. If a pallet stays 31 days in storage, it's automatically charged for the next full 30-day 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 30-day 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 30-day period (31-60), 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 30-day 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 61-90 period.

I just can't get my head around it!

Thanks for any tips

2. 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)

3. Wow, that was quick! Sample attached, thanks!

4. 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!

5. 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 P-R are equivalent to your M-O. P-R 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 30-day intervals). So this calculates the # of 30-day 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 30-day 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 30-day 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 P-R 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•