IF / AND formula Simplification and Maximum Value

[Mike_Sanderson]

Hi, 

I have created a formula that sizes timber lintels depending on the span and loaded dimension.

It works but I am hoping someone can help refine it. 

The "Lintel Thickness" formula is very long. Can this be shortened?

Also, how do I add a user message or some sort of error to indicate when the Lintel exceeds the design limitations?

Ideally I would want it to display and Schedule "SED Specific Engineer Design"

if(and(LD = 2000 mm, Lintel Span < 1401 mm), 90 mm,
if(and(LD = 2000 mm, Lintel Span > 1401 mm, Lintel Span < 2101 mm), 140 mm,
if(and(LD = 2000 mm, Lintel Span > 2101 mm, Lintel Span < 2901 mm), 190 mm,
if(and(LD = 2000 mm, Lintel Span > 2901 mm, Lintel Span < 3601 mm), 240 mm,
if(and(LD = 2000 mm, Lintel Span > 3601 mm, Lintel Span < 4201 mm), 290 mm,
if(and(LD = 3000 mm, Lintel Span < 1201 mm), 90 mm,
if(and(LD = 3000 mm, Lintel Span > 1201 mm, Lintel Span < 1901 mm), 140 mm,
if(and(LD = 3000 mm, Lintel Span > 1901 mm, Lintel Span < 2601 mm), 190 mm,
if(and(LD = 3000 mm, Lintel Span > 2601 mm, Lintel Span < 3301 mm), 240 mm,
if(and(LD = 3000 mm, Lintel Span > 3301 mm, Lintel Span < 3901 mm), 290 mm,
if(and(LD = 4000 mm, Lintel Span < 1101 mm), 90 mm,
if(and(LD = 4000 mm, Lintel Span > 1101 mm, Lintel Span < 1801 mm), 140 mm,
if(and(LD = 4000 mm, Lintel Span > 1801 mm, Lintel Span < 2401 mm), 190 mm,
if(and(LD = 4000 mm, Lintel Span > 2401 mm, Lintel Span < 3101 mm), 240 mm,
if(and(LD = 4000 mm, Lintel Span > 3101 mm, Lintel Span < 3701 mm), 290 mm,
if(and(LD = 6000 mm, Lintel Span < 1001 mm), 90 mm,
if(and(LD = 6000 mm, Lintel Span > 1001 mm, Lintel Span < 1601 mm), 140 mm,
if(and(LD = 6000 mm, Lintel Span > 1601 mm, Lintel Span < 2101 mm), 190 mm,
if(and(LD = 6000 mm, Lintel Span > 2101 mm, Lintel Span < 2701 mm), 240 mm,
if(and(LD = 6000 mm, Lintel Span > 3701 mm, Lintel Span < 3301 mm), 290 mm, 290 mm))))))))))))))))))))

[ToanDN]

What if, say, the span = 1401?

[Mike_Sanderson]

There is no problem when testing it.
Do you think this formula is incorrect?

[ToanDN]

Did you test the span = 1401?  What is the lintel size then?

[Mike.FORM]

You can get rid of all the > requirements in the AND conditional as the previous IF already dictates it is greater than that. If the first IF fails it means either the LD does not equal 2000 or the Lintel Span is > 1400 so you do not need to recheck the > Lintel Span again.

This also solves what @ToanDN mentioned about 1401. As you have it now with a value of 1401 it is probably giving you a value of 290 as it doesn't meet any of the conditionals. 1401 is not < 1401 nor > 1401 it is = to 1401 so to cover that they way you have currently you would need to use < 1401 and > 1400.

if(and(LD = 2000 mm, Lintel Span < 1401 mm), 90 mm,
if(and(LD = 2000 mm, Lintel Span < 2101 mm), 140 mm,
if(and(LD = 2000 mm, Lintel Span < 2901 mm), 190 mm,
if(and(LD = 2000 mm, Lintel Span < 3601 mm), 240 mm,
if(and(LD = 2000 mm, Lintel Span < 4201 mm), 290 mm,
if(and(LD = 3000 mm, Lintel Span < 1201 mm), 90 mm,
if(and(LD = 3000 mm, Lintel Span < 1901 mm), 140 mm,
if(and(LD = 3000 mm, Lintel Span < 2601 mm), 190 mm,
if(and(LD = 3000 mm, Lintel Span < 3301 mm), 240 mm,
if(and(LD = 3000 mm, Lintel Span < 3901 mm), 290 mm,
if(and(LD = 4000 mm, Lintel Span < 1101 mm), 90 mm,
if(and(LD = 4000 mm, Lintel Span < 1801 mm), 140 mm,
if(and(LD = 4000 mm, Lintel Span < 2401 mm), 190 mm,
if(and(LD = 4000 mm, Lintel Span < 3101 mm), 240 mm,
if(and(LD = 4000 mm, Lintel Span < 3701 mm), 290 mm,
if(and(LD = 6000 mm, Lintel Span < 1001 mm), 90 mm,
if(and(LD = 6000 mm, Lintel Span < 1601 mm), 140 mm,
if(and(LD = 6000 mm, Lintel Span < 2101 mm), 190 mm,
if(and(LD = 6000 mm, Lintel Span < 2701 mm), 240 mm,
if(and(LD = 6000 mm, Lintel Span < 3301 mm), 290 mm, 290 mm))))))))))))))))))))

[Mike.FORM]

I took another stab at this to get rid of the and conditionals by checking the LD first and then cycling through the Lintel Span upper limits with an IF statement. You should be able to copy paste the following. 

if(LD = 2000, if(Lintel Span < 1401, 90, if(Lintel Span <2101, 140, if(Lintel Span <2901, 190, if(Lintel Span <3601, 240, if(Lintel Span <4201, 290, 222))))), if(LD = 3000, if(Lintel Span < 1201, 90, if(Lintel Span <1901, 140, if(Lintel Span <2601, 190, if(Lintel Span <3301, 240, if(Lintel Span <3901, 290, 333))))), if(LD = 4000, if(Lintel Span < 1101, 90, if(Lintel Span <1801, 140, if(Lintel Span <2401, 190, if(Lintel Span <3101, 240, if(Lintel Span <3701, 290, 444))))), if(LD = 6000, if(Lintel Span < 1001, 90, if(Lintel Span <1601, 140, if(Lintel Span <2101, 190, if(Lintel Span <2701, 240, if(Lintel Span <3301, 290, 666))))),111))))

In the above,

If the LD = 2000 but the Lintel Span is not less than 4201 it returns 222.

If the LD = 3000 but the Lintel Span is not less than 3901 it returns 333.

If the LD = 4000 but the Lintel Span is not less than 3701 it returns 444.

If the LD = 6000 but the Lintel Span is not less than 3301 it returns 666.

If LD does not = 2000, 3000, 4000 or 6000 it returns 111.

You can use these values to have some model text or a label become visible that states what the issue is.

[curtisridenour]

maybe i am wrong, but this seems like a look up table type of thing.

[ToanDN]

You can cut down the formula considerably (think 1/10) by introducing types based on LD and range parameters based on LD type.  Share your family here I can show you how.

[Mike_Sanderson]

Thanks for the replies. Please see family attached (the formula in the family is in its original form).

@curtisridenour I have never used look up tables, but it could be a possibility because I am trying to replicate the results from a span table. See attached.

[Mike_Sanderson]

This is going back a little while now but @ToanDN did you have a better solution I could be using?