basic opertors

[Tanya]

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

 

[T. Valko]

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.

 

[Héctor Miguel]

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.

 

[Tanya]

Excellent! Thank you very much.

It worked a charm.

I love this group, I have learnt so much from the support of so many...

 

[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'???

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Héctor Miguel]

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.

 

[Tanya]

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]

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.

 

[Tanya]

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 !

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.

 

[Héctor Miguel]

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