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