LOOKUP as User defined function

[Jesper Davidsen]

Hi

I make a lot of simple lookup like in attached sheet. Example I need to lookup the internal diameter of a pipe to calculate the flow speed for a given Nominel dimension.

DN size (Column A)
Wall Thickness Column (Row 2, Column 2=C4:D27, Column 3=E4:F27, Column 4=G4:H27)

Now I use a simple lookup formular: LOOKUP(L12,A4:F27,6,FALSK) but this requires me to manually change the Lookup_vector to the right wall thickness column.

I would like to define a user defined function that returns the internal diameter Di based on inputs:

=Pipe_Di(DN,WTC)

Other finctions could also be usefull =Pipe_D(DN,WTC), =Pipe_t(DN,WTC) but i guess that the recipe is the same.

Once I get this up and running more functions that do actual calculations could also be usefull =Flow_speed(DN, WTC, VFLOW)

The question is how to get started - I and starting from scratch when it comes to user defined functions. Can anyone direct me to a relevant tutorial or better an example file?

/Jesper

[Jesper Davidsen]

Ups forgot to attach file
https://dl.dropbox.com/u/112710/pipe.xlsx

[MrShorty]

How do you decide which column to return? in your example, why column 6 (F) and not columns 4 (D) or 8 (H)? If you can explain the logic, you should be able to make a function/formula for the column number and use that instead of the hard coded 6. In my modification of the example, I've added a cell for this formula (currently it contains the constant 6 because I have no idea how you chose it). If you can figure out a formula for this cell, then it should automatically decide which column to pull from.

[Jesper Davidsen]

Hi

Selecting the right column is a manual decision. Writing the formula in your modification is no problem.

My problem is that I would like to turn this (and other similar) formula into a globally available user defined function and I have no idea where to start I do know that I need to add an xla with some Visual Basic code as an add-in, but don't know any VBA.....

I would like a function Pipe_Di that returns the internal diameter of a pipe based on the two input Nominal Diamerer (DNxxx) and wall thickness column. Hope that every thing is clear and someone can help solve this basic problem.

/Jesper

[MrShorty]

I'm not sure I see how making it a UDF will improve the situation, as you will still have to manually edit something to tell it which column to return from.

Writing UDF's is not difficult, but it sounds like there is going to be a learning curve since, by your own admission, you don't know any VBA. I find that there are two basic skills to programming - decomposing a problem down into individual steps (this is not necessarily language specific), then language specific syntax to describe how to execute the algorithm in a specific language.

This might be a good place to start for learning how to write UDF's http://office.microsoft.com/en-us/ex...001111701.aspx It says it was written for 2003, but, as far as I know, the basic ideas are still the same.

I would also suggest that, as you build the UDF, that you start simple, and work your way up to more complex. So, since cross-sectional area is a part of the calculation, I might start with a UDF that calculates cross sectional area. This will demonstrate the very basic ideas in writing UDF's like - passing arguments to the function and getting a value returned from the function without the complexities of lookups and so on. This might look like

Please Login or Register  to view this content.

Called as =flowspeed(F20) from your sample spreadsheet. From there, you can expand the calculation to do what you need. When you are ready, you can then add a lookup section (I like to use the Excel's MATCH() function, you could also use VLOOKUP()) to get the diameter from the table. See http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx