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!

1

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))))

0

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":

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)

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy