How do I associate calculations with items in a dropdown list?

[stephmon]

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?

 

[Bob Davison]

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