Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
basic 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
basic opertors
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. -- Biff Microsoft Excel MVP "Tanya" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
basic opertors
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. 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. 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
basic opertors
Excellent! Thank you very much.
It worked a charm. I love this group, I have learnt so much from the support of so many... "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Tanya" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
basic opertors
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 "Héctor Miguel" wrote: 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. 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. 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
basic opertors
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Tanya" wrote in message ... Excellent! Thank you very much. It worked a charm. I love this group, I have learnt so much from the support of so many... "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Tanya" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
basic opertors
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
basic opertors
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 "Héctor Miguel" wrote: 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
basic opertors
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
basic opertors
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 "Héctor Miguel" wrote: 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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
basic opertors
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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Basic Chart | Charts and Charting in Excel | |||
Really basic help with a graph | Charts and Charting in Excel | |||
this is probably very basic | Excel Discussion (Misc queries) | |||
Basic If | Excel Worksheet Functions | |||
DV basic help | New Users to Excel |