Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need some assistance with how to write an Excel formula 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 product to be placed in cell S63

Example:

IF A28=LIN,
THEN CELL D63 NEEDS TO BE MULTIPLIED BY 13.80
AND THAT PRODUCT 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  

Any help is greatly appreciated!

share|improve this question
    
To begin with. What sum? The IF function sample: =IF(A1="LIN",B1*13.8) –  Darius Sep 15 '11 at 20:29
add comment

2 Answers

You need to have a separate table with each of the possible values (LIN, LOX, etc.) in one column and the associated conversion factors (13.80, 12.08, etc.) in the next column. So, if this table is in B1:C7, you would enter the following in S63:

=D63*VLOOKUP(A28,B1:C7,2,FALSE)

If you would rather hard-code the values into the formula rather than creating a separate lookup table, you can use the following less elegant formula in S63:

=IF(ISBLANK(A28),"",IF(A28="LIN",13.80,IF(A28="LOX",12.08,IF(A28="LAR",9.671,1.0))))
share|improve this answer
    
+1 and the VLOOKUP is much cleaner :) –  Darius Sep 15 '11 at 20:31
add comment

Try entering this formula in S63

=D63*SUM(
(A28="LIN")*13.8,
(A28="LOX")*12.08,
(A28="LAR")*9.671,
(A28="CO2")*1,
(A28="GH2")*1,
(A28="GN2")*1,
(A28="GO2")*1)

How it evaluates if A28 contains "LOX":

enter image description here

If you're feeling a little adventurous, you can try these formulas in S63:

=D63*CHOOSE(MATCH(A28,{"LIN";"LOX";"LAR";"CO2";"GH2";"GN2";"GO2"},0),13.8,12.08,9.671,1,1,1,1)

or

=D63*VLOOKUP(A28,{"LIN",13.8;"LOX",12.08;"LAR",9.671;"CO2",1;"GH2",1;"GN2",1;"GO2",1},2,0)
share|improve this answer
add comment

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.