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
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. |
#5
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. |
#6
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. |
#7
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 |
#8
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 |
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 |