Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I am trying to create a spreadsheet that will calculate varying sizes. I need it to reference a key where a letter is an established size such as H=1984, S=672, etc. so as that that column can be changed between a series of letters and the formula will automatically convert the equation.

Example:

     A       |     B      |    C     |         D         
Type One     |     H      |    94    |   (C1/B1) = 0.047
Type Two     |     S      |   115    |   (C2/B2) = 0.171

Where as if I switch H to S the formula will automatically change the value and I receive the correct computation.

     A       |     B      |    C     |         D         
Type One     |     S      |    94    |   (C1/B1) = 0.14
Type Two     |     S      |   115    |   (C2/B2) = 0.171

Do I have to make a key section that the formula will reference from depending on what is in the cells?

share|improve this question
1  
Haven't you been able to find a similar solution here on Web Applications? –  Jacob Jan Tuinstra Aug 11 '13 at 10:25

2 Answers 2

You need to create a lookup table where the values for H and S etc. are next to the letters. Then you can use a lookup function to retrieve the values. The lookup function can be nested in a formula.

share|improve this answer
    
Could you add some example code to your answer? –  Vidar S. Ramdal Jan 8 at 16:21

Say create a table with H in G1, S in G2, 1984 in H1 and 672 in H2. In D1:

=C1/vlookup(B1,G:H,2,0)  

and copy down should suit.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.