IF Function with Drop Down Menu

New Contributor

Hi All, 

 

Hope you can help. 

 

I have a drop down menu which contains certain types of Timber (Lumber for my american friends). E.G 4x2, 5x2, 6x2. Based on what is selected I want the cell adjacent to display the timber width, so if i select 4x2 i want the cell adjacent to display 95.... Tried using an If function with no joy. 

 

Any help would be much appreciated. 

7 Replies

@SBolster 

For whos who have no idea about Timber and Lumber could you please explain what is the logic behind this conversion, why 4x2 goes to 95, etc. Do you have some mapping for that or what?

So 4x2 is 4inches by 2 inches, in the UK we use metric so the 4 is about 95cm... its more the formula or how how to get the above to work i'm struggling with

@SBolster , thank you.

 

To multiply one on another

image.png

with

 

=LEFT(D3,FIND("x",D3)-1)*RIGHT(D3,LEN(D3)-FIND("x",D3))

 

After that I didn't catch. 4x2 gives 8 inches, not 4. And 8 inches is about 20 cm, not 95 cm. In general you may use CONVERT() function to wrap above selecting proper units.

so I don't know about UK lumber sizes but in the US a 2x4 (not usually referred to as 4x2) is actually 1.5" x 3.5" so the actual metric size would be 38.1 x 88.9mm or about 3.8 x 8.9 cm. So do you want ACTUAL dimensions or just convert to nominal dimensions. But as I was working on the formula I realize that 4" isn't even close to 95cm, but rather 10.16cm or 101.6mm. So even if you meant mm instead of cm neither the nominal (4") nor actual (3.5") sizes convert to 95cm so does the UK have its own nominal sizes?
As for how there are multiple options using IFS or CHOOSE or you could have the formula actually calculate the conversion (which probably in hind sight not what you need based on the above revelation about 95):
=LEFT(A1,SEARCH("x",A1)-1)*25.4&"x"&RIGHT(A1,LEN(A1)-SEARCH("x",A1))*25.4
So assuming the 95cm was supposed to 95mm and that UK has there own nominal sizes a lookup table might be easiest. add a sheet and create the table and then just use a LOOKUP function based on the drop down select what metric size you want to display.
The US actual sizes are all 1/2" less than their nominal sizes. Are the UK actual all 1/2 cm less then the nominal? That would explain the 95mm nominal would be about 90mm actual and about match the 89mm actual for a 2x4".

@mtarler 

 

It's nothing to do with the sizes that i'm trying to work out. All im trying to do is if i select the Timber Size 4x2 - then 92mm or whatever the "actual" size is in mm doesn't have to be accurate at this stage. I don't need it to work out the sizes e.t.c. I just want to select the timber being used for a specific Job. Then it'll output the width of that timber in the adjacent cell, i was trying the IF function but it wasn't working. Hope i'm making sense  

@SBolster 

It looks like you need to have mapping table of Timber Size  - width, and XLOOKUP() the proper width from it.