Results 1 to 3 of 3

20050623, 23:19 #1
 Join Date
 Jun 2001
 Location
 San Jose, California, USA
 Posts
 1,061
 Thanks
 0
 Thanked 0 Times in 0 Posts
IF statement for nonzero 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 nonzero # 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 nonzero 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

20050624, 07:29 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: IF statement for nonzero 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.

20050625, 01:57 #3
 Join Date
 Jun 2001
 Location
 San Jose, California, USA
 Posts
 1,061
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: IF statement for nonzero 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 leftover 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