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 |

4/18/2010 2:59:45 PM

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 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=196469 http://www.thecodecage.com/forumz

0 |

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 |

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 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=196469 http://www.thecodecage.com/forumz

0 |

4/18/2010 5:52:33 PM

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 "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 > View this thread: > http://www.thecodecage.com/forumz/showthread.php?t=196469 > > http://www.thecodecage.com/forumz >

0 |

4/20/2010 5:10:13 PM

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 --...

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" ...

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...

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...

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. > ...

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...

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 ...

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...

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"...

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...

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...

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...

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...

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...

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...

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, > ...

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? ...

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...

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...

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...

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...

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)...

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&"'!"...

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 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. ...