#### Complex functions

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

 0
JP
4/18/2010 2:59:45 PM
excel.worksheet.functions 4936 articles. 2 followers.

4 Replies
1537 Views

Similar Articles

[PageSpeed] 2

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!

JP Ronse;702259 Wrote:
>
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

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558

http://www.thecodecage.com/forumz

 0
p45cal
4/18/2010 5:16:11 PM
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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"JP Ronse" <fb893760@skynet.be> wrote in message
news:#DhSKfw3KHA.4332@TK2MSFTNGP02.phx.gbl...
> 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
>
 0
Bernard
4/18/2010 5:23:22 PM
Further to my last post on this, as an example, say you wanted a
function to work out the length of the long side of a right-angled
triangle knowing the the lengths of the two shorter sides, using
Pythagoras (the squaw of the hippopotamus is equal to the sons of the
squaws of the other two hides).
Just as a for instance, say you had two cells, left and right, each
containing the length of the shorter side and you wanted the length of
the long side to the right of both cells.
To get the formula right, compose it on a worksheet first. At random I
put the two shorter lengths values in cells B26 and C26 and developed
the formula in D26:
=SQRT(B26^2+C26^2)
A quick test with 3 and 4 as the two shorter lengths gives correctly
5.
Now copy this formula to the clipboard, AND WITH CELL D26 STILL THE
ACTIVE CELL, go into Insert|Names|Define Names (Name Manager in the
Defined Names section of the Formulas tab in the xl2007 ribbon) and
create a new Name, call it HYPOTENUSE and then paste in the formula from
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 calculation
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 a
formula directly into the Refers to box in the Define Name dialog box to
create a named formula. The formula that you enter uses cell references
relative to the active cell�the cell that receives the formula. If you
use the mouse to indicate related cells in the act of building a
formula, however, the references will be absolute.
Figure 3-4 shows a formula (=A1^B1) entered directly in the Refers to
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 cell
references are relative). After this name is defined, entering =Power
into a cell raises the value two cells to the left to the power
represented by the cell directly to the left. For example, if B10
contains 3 and C10 contains 4, entering the following formula into cell
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 worksheet
cell.
When you call up the Define Name dialog box after creating the named
formula, you�ll find that the Refers to box displays a formula that is
relative to the active cell.
For example, if cell D32 is active, the Refers to dialog box will
display:
=Sheet1!B32^Sheet1!C32
Notice that Excel appends the worksheet name to the cells references
used in your formula. This, of course, will cause the named formula to
produce incorrect results if you use it on a worksheet other than the
one in which it was defined. If you would like to use this named formula
on a sheet other than Sheet1, you�ll need to remove the sheet references
fromthe formula (but keep the exclamation points). For example:
=!A1^!B1
After you understand the concept, you may discover some new uses for
named formulas. One distinct advantage is apparent if you need to modify
the formula. You can just change the definition in the Name Box rather
than edit each occurrence of the formula.--

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558

http://www.thecodecage.com/forumz

 0
p45cal
4/18/2010 5:52:33 PM
Hi Bernard, P45Cal,

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

With kind regards,

JP

"p45cal" <p45cal.49mm0o@thecodecage.com> wrote in message
news:p45cal.49mm0o@thecodecage.com...
>
> Further to my last post on this, as an example, say you wanted a
> function to work out the length of the long side of a right-angled
> triangle knowing the the lengths of the two shorter sides, using
> Pythagoras (the squaw of the hippopotamus is equal to the sons of the
> squaws of the other two hides).
> Just as a for instance, say you had two cells, left and right, each
> containing the length of the shorter side and you wanted the length of
> the long side to the right of both cells.
> To get the formula right, compose it on a worksheet first. At random I
> put the two shorter lengths values in cells B26 and C26 and developed
> the formula in D26:
> =SQRT(B26^2+C26^2)
> A quick test with 3 and 4 as the two shorter lengths gives correctly
> 5.
> Now copy this formula to the clipboard, AND WITH CELL D26 STILL THE
> ACTIVE CELL, go into Insert|Names|Define Names (Name Manager in the
> Defined Names section of the Formulas tab in the xl2007 ribbon) and
> create a new Name, call it HYPOTENUSE and then paste in the formula from
> 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 calculation
> 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 a
> formula directly into the Refers to box in the Define Name dialog box to
> create a named formula. The formula that you enter uses cell references
> relative to the active cell-the cell that receives the formula. If you
> use the mouse to indicate related cells in the act of building a
> formula, however, the references will be absolute.
> Figure 3-4 shows a formula (=A1^B1) entered directly in the Refers to
> 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 cell
> references are relative). After this name is defined, entering =Power
> into a cell raises the value two cells to the left to the power
> represented by the cell directly to the left. For example, if B10
> contains 3 and C10 contains 4, entering the following formula into cell
> 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 worksheet
> cell.
> When you call up the Define Name dialog box after creating the named
> formula, you'll find that the Refers to box displays a formula that is
> relative to the active cell.
> For example, if cell D32 is active, the Refers to dialog box will
> display:
> =Sheet1!B32^Sheet1!C32
> Notice that Excel appends the worksheet name to the cells references
> used in your formula. This, of course, will cause the named formula to
> produce incorrect results if you use it on a worksheet other than the
> one in which it was defined. If you would like to use this named formula
> on a sheet other than Sheet1, you'll need to remove the sheet references
> fromthe formula (but keep the exclamation points). For example:
> =!A1^!B1
> After you understand the concept, you may discover some new uses for
> named formulas. One distinct advantage is apparent if you need to modify
> the formula. You can just change the definition in the Name Box rather
> than edit each occurrence of the formula.--
>
>
> --
> p45cal
>
> *p45cal*
> ------------------------------------------------------------------------
> p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
>
> http://www.thecodecage.com/forumz
>

 0
JP
4/20/2010 5:10:13 PM

Similar Artilces:

Worksheet function
Hi all, i am using following code to copy values from Source Workbook to Target Workbook, its working fine. vSourceWS.Range("A1").Copy Destination:=vTargetWS.Range("A1") information at source is in following format: /SCC/4 TargetWorkbook only requires "SCC", following funtion fulfill this requirement MID(A1,(FIND("/",A1)+1),FIND("/",A1,2)-2) How can i achive this goal using VBA, without entering formula in Worksheet. Regards Atif Sub qwerty() s = "/SCC/4" t = Split(s, "/") MsgBox t(1) End Sub --...

want to apply function to numerous cells
HI, Basically I want to divide a number of cells by a number in another cell. So say, A1 through A10 by C3. However, I do not want to make the change permanent, and I want to be able to see what the values would be if C3 changes. So apart from manually going thru and adding the = and /C3 is there a way to map that = xC3 to the original cells. (I know its a circular reference of sorts, but can i write a macro to perform that task for me.. or is there some other way?) Thanks try =a1/\$c\$3 and copy down to a10 -- Don Guillett SalesAid Software donaldb@281.com "ExcelQuestion" ...

Left function #3
I have a reference code that I wish to extract and paste into separate cells. I know that if I use =LEFT(A1,1) will give me the first letter of my code. If I use =LEFT(A1,2) it will put the first two letters in the same cell. What I want is is the 2nd letter to be placed in a separte cell and the third letter to be placed in a separate cell and so on. Can anyone help please? Allan See Excel Help for =MID() -- Gary's Student "Allan" wrote: > I have a reference code that I wish to extract and paste into separate cells. > I know that if I use =LEFT(A1,1) will give me...

Using the kit function
We would like to create some kits using our inventory on hand. How would we create them and have the individual items depleted from stock and go to these kits. We need to be able to know when to order more items to create the kits when they are getting low. Go to Database -> Items -> New Item -> Standard then change item type to Kit, then click on Kit tab and add the items you need, it will be depleted from inventory. Click on attributes and set the re-order point and re-stock level for the items, create a purchase order weekly or daily and select re-order point method to cr...

Excel if function
I do not want to calculate minus in my totals so how can I use the sumif function with non consequent cells. Hi you may provide some more info about your data layout. Maybe you mean =SUMIF(A1:A100,"<0") -- Regards Frank Kabel Frankfurt, Germany "kim" <kim@discussions.microsoft.com> schrieb im Newsbeitrag news:08CF5C52-AE75-4114-8206-C68F2093226B@microsoft.com... > > I do not want to calculate minus in my totals so how can I use the sumif > function with non consequent cells. > ...

need a function to repeat a process
Hello - I have a large list of assessments for a school. Each assessment ha five levels and a count of students for each level - like so: asmt 1 lvl 1 9 lvl 2 13 lvl 3 24 lvl 4 7 lvl 5 1 asmt 2 lvl 1 7 12% =M12/\$M\$17 lvl 2 11 19% =M13/\$M\$17 lvl 3 29 50% =M14/\$M\$17 lvl 4 9 16% =M15/\$M\$17 lvl 5 2 3% =M16/\$M\$17 total 58 for each assessment, I need to total the counts, then determine th percentage of each count. Easy to do for one but a pain to for th whole school. If I could write a single function that used ove again it would save me a ton of time. Thanks in...

Nesting more than 7 IF functions
I have a list with over 20,000 rows in it. Each cell in column D has a number between 1 and 200,000. I would like to set up a formula in column E that will show a letter based on what range the number is in. For example, if the number is between 1 and 500, show "A", if it is between 501 and 1,000, show "B". If there were only 7 number ranges I would use a nested if: =IF(AND(D1>1,D1<500),"A",(IF(AND(D1>500,D1<1000)),"B",(IF....... and so on... The problem is that I have 13 number ranges that I need to create codes for. Any tips? For ...

Public Folder tree
We have some public folder trees that are getting 5-10 levels deep, as they are by default matching our projects drive folder structure. I am just wondering, is there a limit to how deep and wide a public folder matrix can/should go? I can easily see a structure of 20 projects, each as many as a hundred subfolders in a tree up to 10 levels deep, with literally thousands of files in each project. Am I courting disaster? Thanks, Gordon On Tue, 1 Nov 2005 11:02:09 -0800, Gordon Price <GordonPrice@discussions.microsoft.com> wrote: >We have some public folder trees that are getting...

Complex formula change making m,e crazy
I have thsi formula... myFormula = "=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))),""UNKNOWN""," _ & "IF(--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))<999," _ & "VALUE(TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))),""UNKNOWN"...

PostMessage() causes echo in Hook function
I have a program which installs a mouse hook. The hook procedure is in a DLL. Under certain condition I post a WM_RBUTTONUP message. I've put some trace messages in code. Now I'm wondering that my hook function is called TWICE. When it's a *real* mouse message it's only called once. Can someone tell what strange thing happens here? For simplicity I reduced the code to this: From the APP: ================================== void CCtrlPropPage::OnTest() { LPARAM lp = MAKELPARAM (5, 5); PostMessage (WM_RBUTTONUP, MK_CONTROL | MK_SHIFT, lp); TRACE0 ("APP: PostMessage (WM_RB...

USB Function driver for ActiveSync in wince5.0
Hi I am writing Usb function driver with ActiveSync support in Wince5.0 for our hardware. I am facing something different problem. i.e., My driver is working perfectly for one or two times, after that in will get hang on middle of Bulk Out/In transfers. When I was checking the debug messages, i found the following issues, --> For Ex: When serial class driver initiates Bulk IN, in my driver, IssueTransfer function gets called and I initialize the registers for Bulk IN and writing data into data register. But in the middle of initialization, i got one interrupt for Bulk OUT trans...

Date Function?
I want a criteria that will return a list of records within a specified month. The "Month" and the "Datepart" Function don't seem to be working for me. Any advice? "Caravatis" <Caravatis@discussions.microsoft.com> wrote in message news:B3B4C87B-24EA-4B27-A454-17CDAF31751A@microsoft.com... >I want a criteria that will return a list of records within a specified > month. The "Month" and the "Datepart" Function don't seem to be working > for > me. Any advice? SELECT tblMyData.* FROM tblMyData WHERE (((Month([Dat...

Calling a function in a thread from a function in another thread
Hi, I have an application that uses two worker threads. I want to call a function in one thread from a function in another thread. I used PostMessage function but did not work properly. Alternately I dont have a windows handler to be passed as an argument for SendMessage function. Any ideas?? Thanks Functions can be called from a thread, but it has no meaning to say a function is in a thread. Did you mean to say that you want to call a function from one thread at the same time another thread in running that function? If so, you can certainly do this. However, you must take certain steps...

query is too complex error message
does anyone know if access 2007 has increased the capabilities of their queries? i have a query that was built in the 2003 version that keeps giving me the subject line error message. the query includes 7 fields that have a criteria requirements. it works for about a month and then just stops working. any help would be appreciated. thanks,andrea-- Andrea On Tue, 27 Mar 2007 18:13:51 -0700, Andrea <Andrea@discussions.microsoft.com>wrote:>the query criteria is as follows:>>([forms]![frmSearchByMultipleFunctions]![ThisValue5] >or([forms]![frmSearchByMultipleFunctions]![ThisVa...

function symbols
What is the symbol for the inflation rate in excel? Toni, There isn't a symbol. Are you thinking of the RATE function? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Toni" <tsev2003@earthlink.net> wrote in message news:016401c35942\$40d0b880\$a601280a@phx.gbl... > What is the symbol for the inflation rate in excel? one of my classes is on excel and we had a test with one question stating: "22. Assume you are creating a worksheet to calculate projected earnings. You expect to cr...

Range of Factorial Function
Hi, For some analysis I am doing, I tried the following LOG(FACT(229)), and it returned NUM!. I am wondering if 229 is too big a number to compute a Factorial of ? If so, is there an upper limit (something like FACT function can be applied for numbers <= 150) for the FACT function ? Thanks in anticipation, Rushi Patel A little experimantation reveals that FACT(170) is the largest possible, returning a value of 7.2574E+306. -- Ian -- "Rushi" <Rushi@discussions.microsoft.com> wrote in message news:9A23F286-6249-4107-95BF-0E7ACE92E2AC@microsoft.com... > Hi, > ...

Find function broken good
Hi All, My Find function in Excel 2000 has stopped working properly. It fails to find text that is right there. I have experienced this issue before but closing Excel or rebooting used to fix it. Now it doesn't. I cannot ask my desktop guy to reinstall Office for me again; he's done it twice in the last three months and it did not resolve the issues for which I was asking for that. Is there possibly a DLL or other file in which the Find function resides, or on which it depends, that might be corrupted, which I could get from another user's machine and copy it over mine? ...

Older "paste" function not available in 2003?
In some accounting programs, negative numbers have the sign to the right. In Excel 2000 you could paste the number and Excel would recognize it as negative, however, this no longer works in Excel 2003. Is there a way to correct this without using macros or formulas? Hi try using 'Data - Text to columns' -- Regards Frank Kabel Frankfurt, Germany Damon wrote: > In some accounting programs, negative numbers have the > sign to the right. In Excel 2000 you could paste the > number and Excel would recognize it as negative, however, > this no longer works in Excel 2003. I...

now function in a form
On a form in Access 2003, I created an unbound control and inserted the formula =now() The date and time is displayed. We do not want the time, just the date so I selected the ShortDate format in the Properties. Works fine, unless someone clicks the control, in which case the time reappears. The form must closed and reopened to remove the time. Can anyone explain this and suggest a work around. Thanks Marsh "Marsh" <Marsh@discussions.microsoft.com> wrote in message news:B3EAB67B-05ED-44BB-B9B4-CD0456BF0F4A@microsoft.com... > On a form in Access 2003, I created an u...

Printing Complex Workbooks
Hi -- I am wondering if anyone has experienced this issue. Some of my users have created very complex workbooks, with different page orientations, formulas, colors and different page sizes. When these documents are printed (NDPS), Excel crashes. This only happens when the entire workbook is printed. Not the active page. The only solution is to use an older version of the Novell print driver - NDPPNT.DLL. Environment specs: Windows 2000 Excel 2000 Netware 5.1 SP6 Novell Client 4.90 HP PCL 5E (latest firmware on printers, latest version of PCL 5E driver) Novell's stance is if pr...

Nz Function (?)
Hi! I have a mainform with a subform. The subform is based in Q_SO. The Q_SO has >=20 in Age column. The same subform has another a textbox to show us age number. Work fine and only show data if Age >=20. The mainform has a textbox to show age number too. But this textbox if don't show data in subform because Age<20, show #Error. How is possible to resolve this, please. Tnahks in advance. an You may be able to solve the problem with an expression like this: =IIf([Sub1].[Form].RecordsetClone.RecordCount = 0, Null, [Sub1].[Form]![Text0]) Access 2007 will not be able...

Function to return a number of fixed length
Hello, I seem to recall (or maybe dreamed of) a function that reterns a number to a fixed length. For example, if the number in question is 25 and the required length is 4, then the output of the function would be 0025. I realise this can be achieved with cell formatting, but I required this in a function if possible. Kind regards Hi One way =LEFT(REPT("0",10),4-LEN(A1))&A1 -- Regards Roger Govier <tryfanman@googlemail.com> wrote in message news:1170844229.353427.149590@p10g2000cwp.googlegroups.com... > Hello, > > I seem to recall (or maybe dreamed of)...

Sum Indirect function through multiple sheets
Hello! I have been looking through Googgle for a solution but have yet to find it. I am trying to replicate the following 3 dimentional sum formula using the indirect function =SUM(START:END!A1:A5) Suppose I have the following (without the double quotes): in cell C1 I have the label "START" in cell D1, I have the lable "END" in cell E1 I have "A1" in cell F1 I have "A5" These are some of examples that I tried in cell B1 =SUM(INDIRECT("'"&C1&":"&D1&"'!"...

Using complex formula in Diagram,AutoShape,WordArt,Organization Ch
Download and free try AddinTools Create from http://www.addintools.com Make you using formulas and cell reference in Excel objects: diagram, autoshape, wordart, and organization chart. To get thousands of Diagrams, AutoShapes, WordArts, Organization Charts, Charts, Reports, Tabs, Cards, and Forms that contain different values in several minutes! The quantity of results that you can produce depends on the volume of data that saved in worksheets. Fully Utilize The Data In Workbooks. The data in results may source from the same worksheet, and may also source from one or more workbooks and...

need help..Simple, compound, complex, compound-complex examples
Need to write a paragraph on the four sentence types...Just need to have an example of each type....please someone help!! Thanks On 30/05/2010 4:18 PM, Peggylynne wrote: > Need to write a paragraph on the four sentence types...Just need to have an > example of each type....please someone help!! Thanks See http://www.eslbee.com/sentences.htm and some of the other sites that appear when you google for the subject of your post. -- Hope this helps, Doug Robbins - Word MVP Please reply to the newsgroup unless you want to obtain my services on a professional basis. ...