Determining How Many Parts With If & And Functions

[Moshay08]

Alright, here's what im looking at...

\1

As you can see my formula for the current selected cell is quite lengthy and if I add anymore it comes up with an error saying there are too many levels of nesting. What I知 trying to do is come up with a formula that compares the sizes in cells M17 - M21. The grey area, or Glass section, is what I知 currently working on. The formula needs to look at the cells M17 - M21 and determine how many pieces of glass you池e going to need to fill the requirements. If you need more of an explanation, let me know and I値l be glad to elaborate

[beaunydal]

Maybe split the long formula into two or more cells then use the SUM function to extract the result into a single cell.
On the attached workbook your O13 formula has been split up into two shorter formulae in O12 and P12.
SUM(O12:P12) in O13 picks up the final result.

With the shorter formulae in the two cells you will be able to add your other comparisons.

Beau Nydal

[6StringJazzer]

I'm having a difficult time unwinding your formula to understand the underlying intent. It is not at all clear how that series of comparisons tells you how many pieces of glass are needed.

I'm also have these problems diagnosing how your sheet works:

M17:M19 have the same array formula but the formula contains no array. Therefore I don't know why you are using an array formula. All three cells have the same formula referring to the same single cell, so will always have the same value, and your IF statement will always produce a 6.

That formula refers to J20, which is a constant, so will always have the same value.

M20:M21 also have the same formula and therefore the same value.

That formula refers to J21, which is a constant, so will always have the same value.

[Moshay08]

I'm having a difficult time unwinding your formula to understand the underlying intent. It is not at all clear how that series of comparisons tells you how many pieces of glass are needed.

I'm also have these problems diagnosing how your sheet works:

M17:M19 have the same array formula but the formula contains no array. Therefore I don't know why you are using an array formula. All three cells have the same formula referring to the same single cell, so will always have the same value, and your IF statement will always produce a 6.

That formula refers to J20, which is a constant, so will always have the same value.

M20:M21 also have the same formula and therefore the same value.

That formula refers to J21, which is a constant, so will always have the same value.

cell's M17-M19 are defaulted to equal the same amount as well as cells M20-M21. However the user of the form will be able to enter a value in there if need be. If they want a custom width or whatever instead of just the default.

basically what happens is the user enters a width number into the RO H field and a height number into the RO NH field, then the forum defaults to make the openings equal to eachother, however if there is a custom width of one of the windows it can be entered in to replace the default forumla.

[6StringJazzer]

beaunydal's idea of splitting is good if you just need to manage the complexity. It might be possible to simplify the formula if you can describe the logic you used to build it. I still can't figure out how all of that gets you a number of pieces of glass.