Finishing touch: converting quantities to reflect unit of measure

**SFranc** · 09-17-2012, 12:20 PM

HelpBook1.xlsx
This attachment is a representative sample of my workbook.

I’m wrestling with a tricky issue. My workbook helps me inventory quantities of chemical material. I will be filing a report with an agency that requires quantity data to be in coded groups. The sheet already has fields populated with =VLOOKUP set to translate a quantity entered manually into code. I also have columns containing the chemical component, physical state and density.

Here’s where it gets interesting.

The quantity I input requires recalculation based on the physical state of the inventory part. Did ya get that? In other words, the input varies depending on whether the material is solid, liquid, or gas. The purpose of this is to convert the quantity given to the appropriate units of measure. Density fields are also populated using =VLOOKUP.

If the physical state field reads "1," no change is required. If the physical state field reads “2” or “3”, the given quantity must be treated as a numerator and density a denominator. The result of the calculation then must be entered into the =VLOOKUP function which selects a quantity code instead of the given quantity.

Ideas, folks? Thanks in advance,
Sarah

**FDibbins** · 09-17-2012, 12:24 PM

sounds like a fairly simple if() statement would give you what you need?

**SFranc** · 09-17-2012, 12:50 PM

Okay, sure, =IF() is part of the solution. My question: what is the most streamlined method for integrating an =IF() into the workbook without adding additional columns? I resist the idea of adding a third set of columns to independently carryout =IF(). There are already too many fields.

maybe altering =VFLOOKUP() to include an =IF statement?

I appreciate fresh perspectives as I detangle all these logic threads... thanks!

**Pepe Le Mokko** · 09-17-2012, 12:59 PM

Could you please add an example of what you need in your sheet? - Thx

**SFranc** · 09-17-2012, 01:31 PM

Pepe- Thanks for your response. Maybe I don't understand your request. By the time I can provide an example of what I need, I'm pretty sure I'll have the solution. In general terms, I am experimenting with integration of a nested =IF(), or maybe some conditional formatting, in search of a process that will automate the calculation to a greater degree. There are probably lots of ways to get to that end, and I wonder if y'all might come up with a creative solution that I missed.

**Pepe Le Mokko** · 09-17-2012, 02:37 PM

My question was for you to fill in manually an example of what you want achieved. If you find this too much work, so be it.

**SFranc** · 09-17-2012, 04:01 PM

Geez~! Well, my conundrum may be cake to y'all but I feel challenged by it and I appreciate creative alternatives and feedback. I described the solution I'm looking for, and hey, no problem. Don't help, pepe. That's cool. Thanks anyway.

**shg** · 09-17-2012, 05:25 PM

Sarah, I think Pepe was asking to to add formulas to the workbook without any IF functions, with an explanation of why you chose than formula based on state. Then someone might understand the logic, and give you formulas that automatically adapt.