Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
Conversion of Cross-Tab Formatted data to qualify for Pivot Table | Excel Discussion (Misc queries) | |||
How do I create a one variable data table? | Excel Discussion (Misc queries) | |||
Is it possible to create a table inside of a cell? | Excel Discussion (Misc queries) | |||
create space in line chart between points, linked to pivot table | Charts and Charting in Excel |