Microsoft Excell 2002 version Question,

[Brian Weick]

I am running a spread sheet calculation program For the Freud raised panel set I created to automatically give the sizes for the rails/stiles and panels ~ it is working great but if I don't put any information in I get this #DIV/0! error on all the cells - this is the formula I used :
=(B11-B15*2-B17)/(B12)
Any Suggestions
thank you
Brian

[Prashun Patel]

What's in cell B12? The error is telling you that the value in B12 has a value of "0", and that it can't divide by 0. If you send me the program I can probably debug it for you.

[Craig Summers]

I put that in excel, and put a value of one (1) into the B11, B12, B15, B17 Cells, returns an answer of -2

In your equation, if cell B12 is blank or Zero, then you will get that error.

Do you have auto-calc on? Press F9 to check
To change auto calc, follow this path Tools/Options/Calculation, toggle the auto calc button (you can change to manual calc for big spreadsheets)

[Joe D'Attilio]

I believe thisis the formula you should enter into your spreadsheet

=IF(B12=0,0,((B11-B15)*2-B17)/B12)

[Paul Hendrickson]

I believe thisis the formula you should enter into your spreadsheet

=IF(B12=0,0,((B11-B15)*2-B17)/B12)

Ditto

another variation:

=IF(b12=0,"",((B11-B15)*2-B17)/B12)

Conditional statements are possibly the coolest part of excel.

[Brian Weick]

I did get the 0 in the cell but the calculation is wrong- I put an example in the first column- any suggestions?

thanks for the help- this is driving me crazy :eek:
Brian

[Brian Weick]

My problem is I am lucky enough to put it together- where does the time go? Hope you guys can figure this one out- If not I appreciate all the help and I will have to put some time aside to becaome a "master"at excel~ yea- I think not:D
Brian

[Brian Weick]

Yet on how to get rid of that #DIV/0! error???? - let me know if you come up with a solution - Thanks
Brian

[Joe D'Attilio]

HOw many drawer panels, IF there are no drawer panels, then input =IF(B13=0,0,"remainder of formula") = =IF(B13=0,0,(B13*B27/B13))

ANy time you are referencing a cell in a formula, if there are instances where you have a blank or 0 always add this to the beginning of the formula:
=IF(B13=0,0,"remainder of formula") My understanding is that you are creating cabinets where some have only drawers and some have only doors and some may be built with both a drawer and a cab door.

Once you get the first column worked out, simply copy the formulas to the next column.

ALso like I said in a previous PM, you need to watch order of operations when grouping formula functions. for example
B24: =(B21+(B20*2)-B18*2+ 7/8)
In this formula I beleive your problem may be here:
...)-B18*2
The product of B21 + B20*2 =
B20 = blank or 0 : 0 * 2 = 0 + B21: 16 = 16.

Per your next piece of the formula; (B18 * 2) will be calculated 1st based on order of operations= 4
Now you have 16 - 4= 12 now add the 7/8 = 12 7/8

I beleive the correct formula would be:
" =(B21+(B20*2)-B18)*2+7/8 "= 22 7/8

IS this the correct answer - I am guessing as I need to back track through your spreadsheet and guess what the answer should be, as opposed to understanding what exactly, you are trying to figure out.

IF this is not correct I suggest you Pm me with your phone number and I'll walk you through everything and get this crazy spreadsheet calculating the correct dimensions so you can spend more time in your workshop and less time in Excel:D

[Craig Summers]

Yet on how to get rid of that #DIV/0! error???? - let me know if you come up with a solution - Thanks
Brian

Brian

I looked at your spreadsheet ....

There is confusion here about the root cause
The SWCs discussed above that an equation that involves a blank or zero cell needs an "IF" statement, that is, there are no drawer panels in your cabinet, as Cell B13 is <empty> or Zero

~~~~~

I see a different problem based on looking at your spreadsheet:
a) Your original equation in cell B29 is =(B13*B27/B13)
This means you are taking B27 and both multiplying and dividing by B13
Mathematically B13 will cancel itself out, regardless of the actual value of B13, unless it is zero.

b) Is the assumption in your spreadsheet that with all cabinets that have drawers, the width of the drawer will match the width of door? In other words, that there will not be offsets, like 2 doors side-by-side and 3 drawers side-by-side in the same cabinet?

c) If this assumption is correct (door width = drawer width), then change your equation in Cell B29 to: =IF(B13=0,0,B27)
What this means is this
-- If B13 (number of Drawers) is blank or zero, then display a Zero
-- If not, then display the value in B27 (width of Door)

[Brian Weick]

So It is the fact that I am missing the (=IF) statement in my formula equation ~ I just got home from work and I am going to try this. I did post the spread sheet as an attachment, is there a problem in opening it?
thank you for your intelligent input. :)
Brian

[Yves Pinet]

I changed all the formulas in your spreadsheet using If statements and it worked. However, I have to agree that B13*B27/B13 is redundant and should be changed

[Brian Weick]

, Yes I agree- and yes It works flawlessly- I realize i have some redundancy and you are absolutely correct in my opinion~ thats the next phase of the improvement with the flow of the formulas-UPDATED:now the form woks as planned
thanks for all your help ~ if it wasn't for this place and the great people~things just wouldn't be the same anymore-:)
Sincerely,
Brian
Thank you!!!!!!!!:):):)