Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Marseilles
    Posts
    67
    Thanks
    5
    Thanked 1 Time in 1 Post

    Question 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
    Attached Files Attached Files
    Last edited by Marp; 2016-05-27 at 17:02.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 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; 2016-05-27 at 17:01.

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Marseilles
    Posts
    67
    Thanks
    5
    Thanked 1 Time in 1 Post
    Wow, that was quick! Sample attached, thanks!

  4. #4
    Star Lounger
    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!

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    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 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
    Attached Files Attached Files

Posting Permissions

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