What is the best way to create a conversion table in Excel ?
 

I want to create a conversion table where at the prompt of a given measured
value, Excel will return the value in a (host of) new measurement(s).
Example: If for gas, i want to convert 1 cubic meter into kcal, I would
enter 1, choose cubic meter as my initial measurement, and then easily read
off a table what this equals in kcal.

see this url
interestsing


martin a. <martin wrote in message
...
I want to create a conversion table where at the prompt of a given
measured
value, Excel will return the value in a (host of) new measurement(s).
Example: If for gas, i want to convert 1 cubic meter into kcal, I would
enter 1, choose cubic meter as my initial measurement, and then easily
read
off a table what this equals in kcal.

Here is my solution (I don't know the real conversion rates):

A B C D E F G H I J
1 Value unit1 unit2 Conv. Type conv Value conv Result Helper
types rate to from to to column
conv conv
2 gas m3 kcal 3,4 gas m3 3 kcal 10,2 2
3 power Hp kW 0,8

Formulas:

E2: Create a List type Data validation, source =$A$2:$A$3 In place of $3
use the No. of the last row!

F3: Create a List type Data validation, source =INDIRECT(ADDRESS($J$2;2;1) &
":" &ADDRESS($J$2;3;1))

G3: your input cell

H3:
=IF(F2=INDIRECT(ADDRESS($J2;2;1));G2*INDIRECT(ADDR ESS($J2;4;1));G2/INDIRECT(ADDRESS($J2;4;1)))

I3:
=IF(F2=INDIRECT(ADDRESS($J2;2;1));INDIRECT(ADDRESS ($J2;3;1));INDIRECT(ADDRESS($J2;2;1)))

J3: =MATCH(E2;A2:A3;0)+1


€žmartin a.€� ezt Ã*rta:

I want to create a conversion table where at the prompt of a given measured
value, Excel will return the value in a (host of) new measurement(s).
Example: If for gas, i want to convert 1 cubic meter into kcal, I would
enter 1, choose cubic meter as my initial measurement, and then easily read
off a table what this equals in kcal.

I forgot to mention that first you have to select columns A:D and sort them
by Value types (and repeat this step after each addition to the table)!
Regards,
Stefi


€žStefi€� ezt Ã*rta:


Here is my solution (I don't know the real conversion rates):

A B C D E F G H I J
1 Value unit1 unit2 Conv. Type conv Value conv Result Helper
types rate to from to to column
conv conv
2 gas m3 kcal 3,4 gas m3 3 kcal 10,2 2
3 power Hp kW 0,8

Formulas:

E2: Create a List type Data validation, source =$A$2:$A$3 In place of $3
use the No. of the last row!

F3: Create a List type Data validation, source =INDIRECT(ADDRESS($J$2;2;1) &
":" &ADDRESS($J$2;3;1))

G3: your input cell

H3:
=IF(F2=INDIRECT(ADDRESS($J2;2;1));G2*INDIRECT(ADDR ESS($J2;4;1));G2/INDIRECT(ADDRESS($J2;4;1)))

I3:
=IF(F2=INDIRECT(ADDRESS($J2;2;1));INDIRECT(ADDRESS ($J2;3;1));INDIRECT(ADDRESS($J2;2;1)))

J3: =MATCH(E2;A2:A3;0)+1


€žmartin a.€� ezt Ã*rta:

I want to create a conversion table where at the prompt of a given measured
value, Excel will return the value in a (host of) new measurement(s).
Example: If for gas, i want to convert 1 cubic meter into kcal, I would
enter 1, choose cubic meter as my initial measurement, and then easily read
off a table what this equals in kcal.