I use a lot of nest if formulas and they can get quite cumbersome.

I have a setup page in a workbook that has a table for maximum length, and overlap length and a wastage length for scores of different materials.

Here is an example formula that finds the quantity of pieces based on a total lineal metre figure:

=IF(R9=”“,”“,(IF(V9>(2*(VLOOKUP-VLOOKUP)),ROUNDUP)+V9)/VLOOKUP),0),IF(V9>(2*(VLOOKUP-VLOOKUP)),ROUNDUP)+V9)/VLOOKUP),0),IF(V9>(2*(VLOOKUP-VLOOKUP)),ROUNDUP)+V9)/VLOOKUP),0),IF(V9>(2*(VLOOKUP-VLOOKUP)),ROUNDUP)+V9)/VLOOKUP),0),IF(V9>(2*(VLOOKUP-VLOOKUP)),ROUNDUP)+V9)/VLOOKUP),0),IF(V9>(2*(VLOOKUP-VLOOKUP)),ROUNDUP)+V9)/VLOOKUP),0),IF(V9>(1*VLOOKUP),ROUNDUP)+V9)/VLOOKUP),0),1)))))))))

And here is a complimentary formula that finds the actual length of the members from the total lineal metre figure.

=IF(R9=”“,”“,SUM – VLOOKUP)),ROUNDUP+VLOOKUP),-2),IF(V9>(6*(VLOOKUP – VLOOKUP)),ROUNDUP+VLOOKUP),-2),IF(V9>(5*(VLOOKUP – VLOOKUP)),ROUNDUP+VLOOKUP),-2),IF(V9>(4*(VLOOKUP – VLOOKUP)),ROUNDUP+VLOOKUP),-2),IF(V9>(3*(VLOOKUP – VLOOKUP)),ROUNDUP+VLOOKUP),-2),IF(V9>(2*(VLOOKUP – VLOOKUP)),ROUNDUP+VLOOKUP),-2),IF(V9>(1*VLOOKUP),ROUNDUP+VLOOKUP),-2),ROUNDUP)))))))))

I am wondering if there is a simpler way to acheive this? My spreadsheet is getting a bit too cumbersome and I might need to start again but I want to make sure I break down each aspect and make it as simple as possible. For each product type I have allowed 50 different entries for total lineal metres.

This is only a very small part of what the spreadsheet does. I also have created a method of finding all like materials, summing the same lengths and then combining them into lengths in decending order IE: 23/6000mm, 34/5400mm, 12/4800mm

Does anyone have any idea on how to simplify my calcs or am I suck with nested if formulas?

stan101 said:

I use a lot of nest if formulas and they can get quite cumbersome.

I have a setup page in a workbook that has a table for maximum length, and overlap length and a wastage length for scores of different materials.

Here is an example formula that finds the quantity of pieces based on a total lineal metre figure:

=IF(R9=”“,”“,(IF(V9>(2*(VLOOKUP-VLOOKUP)),ROUNDUP)+V9)/VLOOKUP),0),IF(V9>(2*(VLOOKUP-VLOOKUP)),ROUNDUP)+V9)/VLOOKUP),0),IF(V9>(2*(VLOOKUP-VLOOKUP)),ROUNDUP)+V9)/VLOOKUP),0),IF(V9>(2*(VLOOKUP-VLOOKUP)),ROUNDUP)+V9)/VLOOKUP),0),IF(V9>(2*(VLOOKUP-VLOOKUP)),ROUNDUP)+V9)/VLOOKUP),0),IF(V9>(2*(VLOOKUP-VLOOKUP)),ROUNDUP)+V9)/VLOOKUP),0),IF(V9>(1*VLOOKUP),ROUNDUP)+V9)/VLOOKUP),0),1)))))))))

And here is a complimentary formula that finds the actual length of the members from the total lineal metre figure.

=IF(R9=”“,”“,SUM – VLOOKUP)),ROUNDUP+VLOOKUP),-2),IF(V9>(6*(VLOOKUP – VLOOKUP)),ROUNDUP+VLOOKUP),-2),IF(V9>(5*(VLOOKUP – VLOOKUP)),ROUNDUP+VLOOKUP),-2),IF(V9>(4*(VLOOKUP – VLOOKUP)),ROUNDUP+VLOOKUP),-2),IF(V9>(3*(VLOOKUP – VLOOKUP)),ROUNDUP+VLOOKUP),-2),IF(V9>(2*(VLOOKUP – VLOOKUP)),ROUNDUP+VLOOKUP),-2),IF(V9>(1*VLOOKUP),ROUNDUP+VLOOKUP),-2),ROUNDUP)))))))))

I am wondering if there is a simpler way to acheive this? My spreadsheet is getting a bit too cumbersome and I might need to start again but I want to make sure I break down each aspect and make it as simple as possible. For each product type I have allowed 50 different entries for total lineal metres.

This is only a very small part of what the spreadsheet does. I also have created a method of finding all like materials, summing the same lengths and then combining them into lengths in decending order IE: 23/6000mm, 34/5400mm, 12/4800mm

Does anyone have any idea on how to simplify my calcs or am I suck with nested if formulas?

write a script to do it..

inside excel? Can you please expand in what you mean?

stan101 said:

inside excel? Can you please expand in what you mean?

Writing VBA code or recording a macro I’d say.

stan101 said:

inside excel? Can you please expand in what you mean?

I expect he means write a macro :)

You know Excel has a built in programming language, right?

After 90 mins in Sydney traffic I’m not feeling up to interpreting long Excel formulas at the moment, but I’ll have a look later, unless d-s solves it first.

okay, I thought a script other than VBA.

I have a fair bit of VBA written in this spreadsheet to do different tasks, but I amnot really sure on how to approach these nested if forumlas via vba.

Have you ever seen anything similar I could use as a template or study project to create my own?

The Rev Dodgson said:

After 90 mins in Sydney traffic

I don’t envy you.

stan101 said:

okay, I thought a script other than VBA.

I have a fair bit of VBA written in this spreadsheet to do different tasks, but I amnot really sure on how to approach these nested if forumlas via vba.

Have you ever seen anything similar I could use as a template or study project to create my own?

You can write a user defined function to do all the stuff in the formula without the long formula.

I’ll post some code later (it’s my hobby).

Happy to share some of the codes I have cobbled together Rev when / if you have time.

stan101 said:

Happy to share some of the codes I have cobbled together Rev when / if you have time.

Having had a look at how long the formula really is (clicking on quote), it would be handy to have the spreadsheet and see all the data. Can you e-mail a copy to dougaj4 (gmail.com)?

Stan – have a look at:

Long Lookup

I haven’t tested it because I don’t have any data, but it should give you a start.

Also as written it seems to do the same lookup at each level, but that’s also easily edited.

Rev, you have mail.

Thanks for the code.

So I’m guessing I would do something like :

Function LongLU(R_9, V_9, T_6, Dat As Range)
R_9 = Sheets(“Main”).Range(“R9:R60”)
V_9 = Sheets(“Setup Page”).Range(“V9:V60”)
T_6 = Sheets(“Main”).Range(“T6”)

and then create a loop for R_9 and V_9 to go through the range? I’ll need to think about this a bit.

stan101 said:

I have a fair bit of VBA written in this spreadsheet

In that case, writing a user-defined function should be quite straightforward. A user-defined function is simply a public function placed in a standard module that returns a value of a type that can be used as the value of a cell. A user-defined function can also return a Range object for use in formulae that expects a range argument (e.g. MATCH(lookup value, lookup array, match type)) (if a range object is not an expected argument, the Range object’s Value property will be used). However, a user-defined function that returns some other type of object will not work even if it is the argument of another user-defined function that is expecting that object type as an argument.

stan101 said:

Does anyone have any idea on how to simplify my calcs or am I suck with nested if formulas?

Generally speaking, one can simplify complicated formulae by doing intermediate calculations in separate (possibly hidden) cells. But in the case of nested IF formulae, only a single outcome is actually calculated, an advantage that would be lost if all the possible outcomes were placed in separate cells. But if there are parts of the formulae that are common to all the possible outcomes, then these parts should be placed in separate cells.

As for replacing nested IF, I am aware of only three conditional functions: IF, IFERROR, and CHOOSE, and that performing some sort of conditional calculation will require one of these functions . However, it should be noted that IF, IFERROR, and CHOOSE can be placed inside other functions and need not be the outermost function. For example, if each of the options is a formula of the same form but differing in a particular detail, then instead of placing the different versions of the formula in the different options of an IF, one could place the IF inside the formula with the different options being the different particular detail. Thus:

=IF(test, C*(X+A+B), C*(Y+A+B))

becomes:

=C*(IF(test, X, Y)+A+B)

stan101 said:

Rev, you have mail.

Received and replied.

stan101 said:

Thanks for the code.

So I’m guessing I would do something like :

Function LongLU(R_9, V_9, T_6, Dat As Range)
R_9 = Sheets(“Main”).Range(“R9:R60”)
V_9 = Sheets(“Setup Page”).Range(“V9:V60”)
T_6 = Sheets(“Main”).Range(“T6”)

and then create a loop for R_9 and V_9 to go through the range? I’ll need to think about this a bit.

You could do that, but it is set up to use as a user defined function (UDF), so you just enter the function in the top cell and copy down.

Another way to go is to have the complete columns (R and V) as the input range. You can then loop through the function for each row, and write the results to an nx1 array, then assign that array as the function return value. You then need to enter the function as an array function to see all the results (select the whole output range and press Ctrl-Shift-Enter).

Thank you Rev. Very thorough. I am very grateful.

I will need to do some reading on UDF and see how I can utilise them in future.

KJW, thank you for the definition, too.

stan101 said:

KJW, thank you for the definition, too.

There are other things one should know about user-defined functions:

(1): User-defined functions are somewhat restricted in what they can do compared to normal VBA code. For example, a user-defined function can’t write to another cell. These restrictions are deliberately imposed by Excel. If one needs to write to a cell as a result of a user action, then one should use an event handler such as the Workbook SheetChange event placed in the ThisWorkbook module.

(2): If the user-defined function refers to a range that is not specified by an argument, then it is necessary to include the following instruction at the start of the function:

Application.Volatile

This is necessary to ensure that the user-defined function is calculated when the range values that are referred to are changed by a calculation. However, all volatile functions are calculated every time there is any changes, even if the changes will have no effect on the cells containing the volatile functions, and therefore volatile functions will slow the response of the workbook to any changes.

(3): Be aware that properties of a Range object other than the Value property (e.g. the Text property) may update late during a calculation so that an outdated value may be read at the time the user-defined function is calculated.