using a word to determine a numeric value in a formula

[Sampzen]

I want to utilize a drop down menu to populate a cell. In another cell I need to create a formula that changes certain criteria based upon the word choosen in a drop down list.

For example cell c2 is an application rate of 32, c3 contains a drop down list of Fl. ounces, gallons, ounces, cell c4 acounts for acres. In cell c6 I want to calculate the amount of product needed by formulating my rate (c2) x acres (c4) / "x" amount determined by whatever is chosen in cell c3, i.e. fl ounces =128.

So 32 (c2) x 1000 (c4) = 32000 oz / 128 (c4 based on choosing fl. ounces) = 250 gallons

[MrShorty]

In a generic sense, I would generally use a lookup table with a lookup function to look up "word -> number" relationship.

However, in the specific case of unit conversions, I like to use the CONVERT() function -- as long as all the units I want to be able to use are built into the CONVERT() function. If C2 is 32, C3 is "oz" (the abbreviation used by the CONVERT() function for fluid oz which you can control in your drop down list), C4 is 1000, then the formula in C6 can be =CONVERT(C2,C3,"gal")*C4 to give you gallons/acre. The key is to make sure your abbreviations in C3 are all valid unit abbreviations used by the CONVERT() function. CONVERT() function help file which includes the allowed abbreviations: https://support.office.com/en-us/art...d-666c810f9af2

[Roel Jongman]

make a reference table and do a vlookup for the value based on the words.


FL ounces | 128
Gallons | 1
ounces | ?

Please Login or Register  to view this content.

that should solve your problem..