Complex functions

[JP Ronse]

Hi All,

Suppose you have a very complex and long function that is also subject to a
condition, then you will/can have something like:

=if(condition(complex function), complex function, other complex function)

It can have more lines in the formula bar, so readability below 0 Kelvin.

Is there a way to 'name' a function?

=If(condition(myfunc = complex function), myfunc, other complex function)




With kind regards,

JP

 

[p45cal]

Yes you can put a formula into an Excel Name (the same as you can give
a range an Excel Name. Very powerful it can be too. Do a Google for:
Excel "Naming Formulas".

Another way is to create user-defined-functions, like a macro but it
just returns a value. You'll be able to use it like the built in
functions, passing ranges/parameters/arguments to it to get a single
result. It can be even more powerful than Names, and I think you might
even be able to combine the two!

 

[Bernard Liengme]

All names in the sense you are using it are actually functions.
Thus if I select a cell (say B2) and type VAT in the Name box and press
Enter (or name that cell in any other way), I get something like =Sheet1!B2
when I use Names | Paste List. So you see VAT in the name of a function ---
its definition begins with an = sign

Here is an example of a name using other names. A2 is given the name a, and
b2 the name b
Then I define a function called myfun1 as being ==(a>b)*5+(b>a)*11
In any cell (not A2 or B2), I type =myfun1 and it will return either 5 or 11
I could have defined the function as =IF(a>b,5,11) and got the same result

Have a go and come back to this thread if more help is needed
best wishes

 

[p45cal]

Further to my last post on this, as an example, say you wanted
function to work out the length of the long side of a right-angle
triangle knowing the the lengths of the two shorter sides, usin
Pythagoras (the squaw of the hippopotamus is equal to the sons of th
squaws of the other two hides).
Just as a for instance, say you had two cells, left and right, eac
containing the length of the shorter side and you wanted the length o
the long side to the right of both cells.
To get the formula right, compose it on a worksheet first. At random
put the two shorter lengths values in cells B26 and C26 and develope
the formula in D26:
=SQRT(B26^2+C26^2)
A quick test with 3 and 4 as the two shorter lengths gives correctl
5.
Now copy this formula to the clipboard, AND WITH CELL D26 STILL TH
ACTIVE CELL, go into Insert|Names|Define Names (Name Manager in th
Defined Names section of the Formulas tab in the xl2007 ribbon) an
create a new Name, call it HYPOTENUSE and then paste in the formula fro
the clipboard into the RefersTo: field. OK out of this dialogue box.
Back on the worksheet, you can type
=Hypotenuse
into any cell and it will try and return the length of the calculatio
using the two cells to its left.

I was surprised to see J Walkenbachs 2002 Power Programming book here:
http://tinyurl.com/y24qhol
where he says this on naming formulas:
--Besides naming cells, ranges, and constants, you can also enter
formula directly into the Refers to box in the Define Name dialog box t
create a named formula. The formula that you enter uses cell reference
relative to the active cell—the cell that receives the formula. If yo
use the mouse to indicate related cells in the act of building
formula, however, the references will be absolute.
Figure 3-4 shows a formula (=A1^B1) entered directly in the Refers t
box in the Define Name dialog box. In this case, the active cell is C1
so the formula refers to the two cells to its left (notice that the cel
references are relative). After this name is defined, entering =Powe
into a cell raises the value two cells to the left to the powe
represented by the cell directly to the left. For example, if B1
contains 3 and C10 contains 4, entering the following formula into cel
D10 will return a value of 81 (3 to the 4th power):
=Power
Figure 3-4: You can name a formula that doesn’t appear in any workshee
cell.
When you call up the Define Name dialog box after creating the name
formula, you’ll find that the Refers to box displays a formula that i
relative to the active cell.
For example, if cell D32 is active, the Refers to dialog box wil
display:
=Sheet1!B32^Sheet1!C32
Notice that Excel appends the worksheet name to the cells reference
used in your formula. This, of course, will cause the named formula t
produce incorrect results if you use it on a worksheet other than th
one in which it was defined. If you would like to use this named formul
on a sheet other than Sheet1, you’ll need to remove the sheet reference
fromthe formula (but keep the exclamation points). For example:
=!A1^!B1
After you understand the concept, you may discover some new uses fo
named formulas. One distinct advantage is apparent if you need to modif
the formula. You can just change the definition in the Name Box rathe
than edit each occurrence of the formula.--

 

[JP Ronse]

Hi Bernard, P45Cal,

Thanks both for your input.

This gives indeed some nice features to write readable functions. I'm going
to play with it.


With kind regards,

JP