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

[martin a.]

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.

[R.VENKATARAMAN]

see this url
interestsing


martin a. <martin [email protected]> wrote in message
news:[email protected]...
> 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.

[Stefi]

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(ADDRESS($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.

[Stefi]

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(ADDRESS($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.