Writing function with multiple (13) if/and/then statements

Here's my 2¢:

So to add a little more information,

Imagine this function is almost like an integrator. For each dx, it will integrate the area under the curve up to that point. For my program, I need it to calculate SA every dh, where dh is the change in height on the wall, and I have dh set to 1/4". So in the column that says Height, it is doing my dh intervals for me. The Surface Area column will calculate the surface area for the "dh" given in that row.

this is still confusing im sure, so...

In my example, in row 10, where "Height"=8, the SA Column will calculate the surface area of the wall from 0 to 8, subtracting any surface areas of windows that may fall from 0-8, INCLUDING FRACTIONS OF A WINDOW. This last part I capitalized because it is the reason I have so many criteria (i.e. does dh cut through window 1, does dh fall between windows 1 and 2, does dh cut through window 2, does it fall between 2 and 3, and so on....)

• if dh falls below the first window, my formula will simply be (width of wall x dh)
• if dh falls in the range of the first window my formula will be ((width of wall*dh)-((dh-height TO first window)*width of window)
• if dh falls in between the first and second windows the formula will be ((width of wall*dh)-(height OF first window*width of first window))

and so on.....

Now I will be creating a similiar formula with 6 "windows", so you can see how this would be annoying to type into the formula bar.

Please, any one know a quick easy way?

*aside- there is a chance multiple windows will exist on same horizontal line of building face

How about something like this:

JOHNROK 2012-07-31.xlsx

where you only need to change the ranges in column G to accomodate more windows.

Originally Posted by CheshireCat

How about something like this:

Attachment 171578

where you only need to change the ranges in column G to accomodate more windows.

Looking through it, it's a little over my head but it appears to be working correctly. I believe column H is my desired column, I'm just confused a little as to what column G is doing.... the sumproduct(.....) part is what I don't understand. Could you explain what that is doing, so that I may adjust it to suit my actual desired task?

Also, thank you very much for your input.

PS, will this work if two windows overlap at the same wall height? (i.e. two window centers are offset by 1 foot vertically, but are 2 foot tall windows...)

The formula in helper column G is calculating the area for each inch of height as 60 minus whatever width window that shares the same height.

The SUMPRODUCT looks at the wall height F2 and checks to see which windows are at the same height from the table B4:B8 and C4:C8.

If you want to add more windows, just put them in the table, then adjust the ranges in the formula at G2 to change the B8, C8, and D8 to B9, C9 and D9, or whatever.

CheshireCat,

I've been looking through this now to see how I can edit it for my own use, and I'm not sure... If this were volume instead of area, and each window had a different thickness, could you still use this to see how much volume the window takes out of the wall at each different height.

Also, I wasnt sure if this would work with multiple windows on the same plane but offset slightly.


Thanks again for your help

JOHNROK,

As your question originally requested surface area, not volume, I would have to agree that expecting an "area" solution to provide a "volume" answer would be unrealistic. Especially if you wanted to identify a different change in volume for each window. I think a different solution would be required.

As for multiple windows on the same plane; I recommend you experiment with some sample data and see if it gives you the expected answer.

and originally I believed that example would work. Now that I am looking through, it would be more similar to Volume than surface area, so if that could be adjusted to Volume that would be great, if not thats ok too...... thanks for your help. CheshireCat, I apologize that I am not able to state my ultimate intentions outright, but I am working with company property and am not able to disclose much....




anyone else have any good solutions to this problem? it is more similiar to the wall/window problem with VOLUME instead of Surface area to be calculated.