Can you help me with setting up an Excel formula?

Lee September 15, 2011
Pinterest Stumbleupon Whatsapp
Ads by Google

What formula would I use for the below:

In cell A28 one of the following will be entered: LIN, LOX, LAR, CO2, GH2, GN2 OR GO2

Depending on which is entered in cell A28 will have a conversion value that needs to be multiplied by the value in cell D63 and that sum to be placed in cell S63

Example: IF A28=LIN, THEN CELL D63 NEEDS TO BE MULTIPLIED BY 13.80 AND THAT SUM PLACED IN CELL S63

LIN = 13.80
LOX = 12.08
LAR = 9.671
CO2 = 1.0
GH2 =1.0
GN2 = 1.0
GO2 = 1.0

Thanks in advance for the help!

  1. Sks3286
    September 16, 2011 at 7:46 am

    You can use vlookup. In a hidden sheet write down the codes that would go in A28 and the corresponding multiplication factors. Then just use vlookup to get the values. Something like S63 = D63*VLOOKUP(A28,sheet2!$A$1:$B$7,2)
    hope this helps

    • Nelson Maldonado
      September 16, 2011 at 1:04 pm

      The only thing I'd add to Sks3286's answer is that when you use a vlookup, the value that is entered in cell A28 has to be exact to the values listed on the hidden lookup table.  If the values aren't exact, then the lookup won't work.  

      One way to guarantee that the vlookup work is to make cell A28 a data validation cell, where the values can a) either be chosen from a drop-down list or b) give an error that will let you know that the value that was entered does not match any of the items on the table.

      I'd me more than happy to clarify...you can always direct message me on twitter @nmaldonado:twitter 

  2. Nkyadav
    September 16, 2011 at 6:24 am

    Lee,
     
    In cell S63, the following formula should work to give you the right answer based on cell A28:
     
    =IF(A28="LIN", D63*13.8, IF(A28="LOX", D63*12.08, IF(A28="LAR", D63*9.671, IF(A28="CO2", D63*1, IF(A28="GH2", D63*1, IF(A28="GN2", D63*1, IF(A28="GO2", D63*1)))))))
     
    There's probably an easier way to do this, and this does not account for if none of those values is entered in A28, so....