Results 1 to 3 of 3
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IF statement for non-zero data (Excel 2003)

    The subject doesn't do much to explain what I need very well so let me explain... I have 5 yrs of quarterly data (so 40 cells) that represent the # of boards a given customer needs to complete a chassis. Each chassis can hold 8 boards so depending on capacity needs, I'm calculating how many (and when) another chassis is needed to accommodate the extra boards. They don't want to buy a chassis until it's needed of course.

    For example, a chassis holds 8 boards and given some capacity requirement, the customer needs to purchase 183 boards to meet this capacity. This means that 22 chassis are needed (183 / 8 rounded down = 22) to hold these boards. These 22 chassis are filled with 183 boards but there are 7 left over boards that will go into chassis #2 (with one slot left over). So initially this customer needs to purchase two chassis, one of which is full (8 boards) and the 2nd one has 1 empty slot. As time goes on and more capacity is needed I need to determine at what point in time another chassis is needed.

    I've tried using nested IF statements to fetch the last non-zero # of extra boards but it's getting beyond what I can comprehend in a formula. How can I write a formula to fetch the last non-zero data (from some previous quarter) and use that to determine the # of chassis that need to be purchased for this quarter (based on some known capacity requirement)?? Are you lost yet?? <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    See attached sample workbook. The shaded quarterly data is correct and the other area is my scratch pad trying to figure out the calc needed.

    Thnx, Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF statement for non-zero data (Excel 2003)

    I'm sorry, I'm confused by your description
    <hr>For example, a chassis holds 8 boards and given some capacity requirement, the customer needs to purchase 183 boards to meet this capacity. This means that 22 chassis are needed (183 / 8 rounded down = 22) to hold these boards. These 22 chassis are filled with 183 boards but there are 7 left over boards that will go into chassis #2 (with one slot left over). So initially this customer needs to purchase two chassis, one of which is full (8 boards) and the 2nd one has 1 empty slot. As time goes on and more capacity is needed I need to determine at what point in time another chassis is needed.<hr>
    (bold applied by me)
    Why into chassis #2? I'd say into chassis #23. And - probably related - why would the customer need to purchase two chassis - again I'd say 23.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF statement for non-zero data (Excel 2003)

    Yes you're right... the 1st purchase is for 23 chassis. I wrote this example one way then later changed my numbers without changing the example so that's why they don't match. <img src=/S/bash.gif border=0 alt=bash width=35 height=39> I've been working on this and think I've got it figured out. Part of the problem was that the person who asked me to do this was convinced that she needed some complex nested (very nested) IF statements to handle cases where the # of purchased chassis happened to be 0 for a given quarter. This seemed overly complex to me at the time. Later when I was looking at the problem on my own - without her hovering over me - I decided it wasn't as complicated as she was leading me to believe (or I have totally misunderstood the problem to be solved).

    So, put simply for the 1st four quarters I have (for 8 boards/chassis)
    <pre> Q1 Q2 Q3 Q3
    Total Boards Needed 183 402 663 963
    No. of New Boards Purchased 183 219 261 300
    No. of Complete Chassis (calc) 22.875 50.25 82.875 120.375
    No. of Complete Chassis (actual) 23 51 83 121
    No. of left-over Boards 7 2 7 3
    No. of Chassis to Purchase in Qtr 23 28 32 38</pre>

    I tried it with other values (for capacity and boards/chassis) and my formulas seem correct. I filled it out for the 5 year period and sent her a copy to review. I'll consider this post to be closed for now.

    I should of done this on my own more before posting this. Sorry 'bout that.
    Thnx, Deb

Posting Permissions

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