Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I associate calculations with items in a dropdown list?
Hello,
I'm putting together a simple calculator in an Excel 2003 spreadsheet and would like to simplify it further. The idea would be to have a dropdown list, populated with factor prefixes "kilo, mega, giga, etc." If the user has a number such as 76MHz, he would enter 76 in column B and select "MHz" from the dropdown list in column C. Then, column D would take the value from column A (an energy) and divide it by B times the factor e.g. A/(B*1,000,000). It would also be very handy for the result to be expressed by "name" e.g. 250 pico Joules, instead of 2.5E-10. Tips, suggestions? -- Cheers, Stephen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I associate calculations with items in a dropdown list?
This might get you started or give you some ideas....
In an unused area of your spreadsheet, arrange your prefixes and factors in 2 columns, prefixes on the left and factors on the right. Prefixes Factor deca 10 kilo 1000 mega 1000000 Select the range with the prefixes and name it something like "prefixes" Select the range with the prefixes and the factors and name it. I'll use "table" Select the area in column C where you wish to have the dropdown lists to be available. Then go to Data/Validation. From the Settings tab under Allow: choose List and then in the Source box type "=prefixes" without the quotation marks. Click OK and now the dropdown boxes should be set up. To get the appropriate multiplier, use a VLOOKUP from the "table" range based on the prefix chosen. You could choose to display the multiplier in a cell (another column) or keep it hidden as in your description. The basic lookup to retrieve the factor (top row) would be: =VLOOKUP(C1,table,2,FALSE) To add the other columns for your equation it would be: =A1/B1*VLOOKUP(C1,table,2,FALSE) To avoid errors from cells without and entry yet (blank cells will generate an error): =IF(ISERROR(A1/B1*VLOOKUP(C1,table,2,FALSE)),"",A1/B1*VLOOKUP(C1,table,2,FALSE)) I am not that familiar with the names you need like pico Joules, but that certainly could be added easily. Might need to add another column to the "table" range for that. To add the corresponding prefix to the above result, just add &" "&C1 to the above formula, for example. That will add the text to the end of the equation result. However, that might cause a rounding problem for your result, by that I mean way too many decimal places. To round it down, use the ROUND function as such: =ROUND(IF(ISERROR(A1/B1*VLOOKUP(C1,table,2,FALSE)),"",A1/B1*VLOOKUP(C1,table,2,FALSE)),3)&" "&C1 Change the number 3 in the above formula to adjust how many decimal places you want to have. Hope this helps you. Bob "stephmon" wrote in message ... Hello, I'm putting together a simple calculator in an Excel 2003 spreadsheet and would like to simplify it further. The idea would be to have a dropdown list, populated with factor prefixes "kilo, mega, giga, etc." If the user has a number such as 76MHz, he would enter 76 in column B and select "MHz" from the dropdown list in column C. Then, column D would take the value from column A (an energy) and divide it by B times the factor e.g. A/(B*1,000,000). It would also be very handy for the result to be expressed by "name" e.g. 250 pico Joules, instead of 2.5E-10. Tips, suggestions? -- Cheers, Stephen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dropdown list contains 5 items want to add 100 how | Excel Discussion (Misc queries) | |||
dropdown list contains 5 items want to add 100 how | Excel Discussion (Misc queries) | |||
select multiple items from a dropdown list | Excel Worksheet Functions | |||
Dropdown list in PivotTable has deleted items. How to omit? | Excel Discussion (Misc queries) | |||
is it possible to re-size or format list items in dropdown box? | Excel Discussion (Misc queries) |