 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008basic opertors
Hi Can anyone tell me if it is possible to create a multiplication table where you can change the table from multiplication to addition or division by changing just one cell? I have created the following formula where the values for A2 = 1 and B1 =1: =A2&A1&B1 the problem I have is the value is read as a text string!!! I have tried Indirect function and I have the same problem. thanks in advance. Tanya There's no way to do it like you want with built-in functions. Here's one way: A1 = drop down list with these selections: Multiply Divide Add Subtract B1:F1 = 1,2,3,4,5 A2:A6 = 1,2,3,4,5 For division I'm thinking you'd probably want to round the result: Enter this formula in B2 and copy across to F2 then down to row 6: =ROUND(CHOOSE(MATCH($A$1,{"multiply","divide","add","subtract"},0),B$1*$A2,B$1/$A2,B$1+$A2,B$1-$A2),2) Simply make a selection from the drop down and the appropriate math operation takes place.  SignatureBiff Microsoft Excel MVP > Hi > Can anyone tell me if it is possible to create a multiplication table [quoted text clipped - 13 lines] > > Tanya hi, all ! how about using xl-4 macro-function "evaluate" into a named-formula ? (i.e.) 1) fill: 1,2,3,4,5 into [B2:F2] 2) fill; 1,2,3,4,5 into [A3:A7] 3) select [B3] > insert / name / define... name: Bas.Op formula: =evaluate(!$a3&!$a$1&!b$2)+0*now() 4) [B3] =bas.op (the name used in step 3) and {enter} 5) fill B3-formula into range [B3:F7] -> use [A1] as the "container" of your "operator" (+, -, *, /, ^, ...) (just in case) xl-2002 is a minimum requirement :-( hth, hector. > T. Valko wrote in message ... > There's no way to do it like you want with built-in functions. [quoted text clipped - 10 lines] > =ROUND(CHOOSE(MATCH($A$1,{"multiply","divide","add","subtract"},0),B$1*$A2,B$1/$A2,B$1+$A2,B$1-$A2),2) > Simply make a selection from the drop down and the appropriate math operation takes place. >> Tanya wrote in message ... >> Can anyone tell me if it is possible to create a multiplication table where you can change the table from multiplication to addition or division by changing just one cell? >> I have created the following formula where the values for A2 = 1 and B1 =1 >> =A2&A1&B1 >> the problem I have is the value is read as a text string!!! >> I have tried Indirect function and I have the same problem. Thank you for your workings, unfortunately it didn't work. I am using Excel 2003 and have all the add-in's loaded. I've never heard of the function 'evaluate'??? cheers Tanya > hi, all ! > [quoted text clipped - 40 lines] > >> the problem I have is the value is read as a text string!!! > >> I have tried Indirect function and I have the same problem. hi, Tanya ! > Thank you for your workings, unfortunately it didn't work. > I am using Excel 2003 and have all the add-in's loaded. > I've never heard of the function 'evaluate' ? sorry if I have not been clear enough - 'evaluate' is from the old fashion xl-4 macro language (still usable in names) - perhaps when you have a little more time to (spend in) follow the previous post steps -?- (it's working for me) ;) if any doubts (or further information)... would you please comment ? regards, hector. Hi Hector, I am by no means a power user of VBA but I do have some understanding of it. However, having said that your xl-4 macro language makes no sense to me what so ever. You solution is written like a formula? Please be more explicit, I would like to be able to understand what you are trying to communicate. regards Tanya > hi, Tanya ! > [quoted text clipped - 12 lines] > regards, > hector. hi, Tanya ! > I am by no means a power user of VBA but I do have some understanding of it. > However, having said that your xl-4 macro language makes no sense to me what so ever. > You solution is written like a formula? Please be more explicit > I would like to be able to understand what you are trying to communicate. just follow this steps: -> use [A1] as the "container" of your "operator" (one single-sign per time: +, -, *, /, ^, ...) (the named-formua in step 3 will use this reference in an absolute-row&column notation) if necessary, preceed the divisor / (when been the operator) with an apostrophe or a space 1) fill [B2:F2] with: 1,2,3,4,5 (the named-formua in step 3 will use this reference in an absolute-row notation) 2) fill [A3:A7] with: 1,2,3,4,5 (the named-formua in step 3 will use this reference in an absolute-column notation) 3) select [B3] (it is importan to be the activecell when)... do a (menu) insert / name / define... name: Bas.Op formula: =evaluate(!$a3&!$a$1&!b$2)+0*now() -> note the "!" sign preceeding every cell-reference and the last part "...+0*now()" is to make it "volatile" with re/calculations of the model/sheet/workbook 4) in cell [B3] "call" the name used in step 3 and {enter} (i.e. =bas.op) 5) fill [B3:F7] the same as B3 (i.e. =bas.op) and/or copy/drag/... the note regarding xl-2002 as a minimum requirement is due to (under certain circumstances) previous versions might *crash* with some xl-4 macro-functions while used as this proposal :-(( if any doubts (or further information)... would you please comment ? regards, hector. Hi Hector Thank you, it worked this time, I understood all except step 3 the first time. I have named cells/ranges before but didn't realise you could use a formula in the 'Refers to...' This is much like writing a function and calling it... A very interesting lesson! Thank you again. Regards Tanya > hi, Tanya ! > [quoted text clipped - 33 lines] > regards, > hector. Tanya, I'm glad to be in help (and thanks to you, for posting-back) regards, hector. > ... it worked this time, I understood all except step 3 the first time. > I have named cells/ranges before but didn't realise you could use a formula in the 'Refers to...' > This is much like writing a function and calling it... A very interesting lesson! Thank you again. > Regards > Tanya (sniped from previous posts)... >>> I am by no means a power user of VBA but I do have some understanding of it. >>> However, having said that your xl-4 macro language makes no sense to me what so ever. [quoted text clipped - 27 lines] >> the note regarding xl-2002 as a minimum requirement is due to (under certain circumstances) >> previous versions might *crash* with some xl-4 macro-functions while used as this proposal Excellent! Thank you very much. It worked a charm. I love this group, I have learnt so much from the support of so many... > There's no way to do it like you want with built-in functions. > [quoted text clipped - 36 lines] > > > > Tanya You're welcome. Thanks for the feedback! SignatureBiff Microsoft Excel MVP > Excellent! Thank you very much. > [quoted text clipped - 42 lines] >> > >> > Tanya |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.