Building a formula that correlates to a drop-down list

[mbagronomy]

I sell agricultural chemicals and I am trying to build a spreadsheet that will figure "total product needed" by multiplying acres x use rate then dividing by a conversion number that is respective to each measurement that I have in my dropdown list. In my dropdown list I have pints, quarts, lbs, fl. ounces, dry ounces, etc based on what the formulation of each product is. I want the Total Units column to figure "total gallons" needed if the product is a liquid formulation, and "total pounds" needed if the product is a dry formulation. How would I build a formula for the Total Units column to recognize which measurement is selected in my dropdown list and use the correct conversion divisor according to the measurement selected?

Conversions used would be:

1 gallon = 8 pints, 4 quarts, 128 fl. ozs
1 pound = 16 dry ozs

For Example:

The product Keystone is a liquid formulation.

[(335 acres * 2 quarts) / 4 quarts per gallon) =167.5 gallons

<table style="border-collapse: collapse;" width="481" border="0" cellpadding="0" cellspacing="0" height="64"><col style="width: 48pt;" width="64" span="7"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Product</td> <td class="xl65" style="width: 48pt;" width="64">Acres</td> <td valign="top">
</td><td class="xl65" style="width: 48pt;" width="64">Use Rate</td> <td valign="top">
</td><td class="xl65" style="width: 48pt;" width="64">Measurement</td> <td valign="top">
</td><td class="xl65" style="width: 48pt;" width="64"> Total Units</td> <td class="xl65" style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">
</td> <td class="xl65">
</td> <td valign="top">
</td><td class="xl65">
</td> <td valign="top">
</td><td class="xl65">
</td> <td valign="top">
</td><td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Keystone </td> <td class="xl65">335</td> <td valign="top">
</td><td class="xl65">2</td> <td valign="top">
</td><td class="xl65">Quarts</td> <td valign="top">
</td><td class="xl65">167.5</td> <td class="xl67">
</td> </tr> </tbody></table>

 

[Oaktree]

Welcome to the board.

Have a look at the CONVERT function in Excel Help (and note that CONVERT requires the analysis toolpak add-in).

Then, so long as your dropdown lists have the same abbreviations that the CONVERT function is expecting, you can just use =A1*CONVERT(B1,C1,"gal")

where A1 has your acres, B1 has the number of quarts/pints/whatever, and C1 has the type (quart/pint/whatever) that corresponds to B1.

 

[mbagronomy]

Excellent!! This is exactly what I was wanting. Thank you very much!!