Nonblank values from a row and the corresponding values from another row to use in formula

Maybe this:

Formula:

Please Login or Register  to view this content.

What i want is to determine which cells in row 5 are not blank and then just pick those values in the formula. In my given example cell B5 and D5 are not blank, so i picked them in the formula. My formula should determine which cells are non-blank and use them in the formula. The above formula was just an example where B5 and D5 were non-blank. So the formula should have the following logic:

IF (AND(B5<>"",C5<>"",D5="",E5=""),B1 + ((C1 - B1) * (10 - B5) / (C5 - B5)) ELSE

IF(AND(B5<>"",D5<>"",C5="",E5=""),B1 + ((D1 - B1) * (10 - B5) / (D5 - B5)) ELSE

IF(AND(C5<>"",D5<>"",B5="",E5=""),C1 + ((D1 - C1) * (10 - C5) / (D5 - C5)) ELSE

and so on - so the formula should check which 2 cells are not blank and take it from there. The above approach would be a quite cumbersome since it would have about 6 IF conditions which is not a good way to write a formula)

Hope this is clear

Thanks G

Here is a suggestion using a "helper" area in G1:H2 (it can be located wherever is convenient)

The helper column formulae pick out the relevant 4 numbers and so the solution cell, B6, contains your equation which now only references the "helper" area.

The helper area formulae are similar but not quite identical:
G1: =INDEX(B1:E1, AGGREGATE(15, 6, {1,2,3,4}/ISNUMBER(B5:E5), 1))
G2: =INDEX(B5:E5, AGGREGATE(15, 6, {1,2,3,4}/ISNUMBER(B5:E5), 1))
H1: =INDEX(B1:E1, AGGREGATE(14, 6, {1,2,3,4}/ISNUMBER(B5:E5), 1))
H2: =INDEX(B5:E5, AGGREGATE(14, 6, {1,2,3,4}/ISNUMBER(B5:E5), 1))

B6: =G1 + ((H1-G1)*(10-G2)/(H2-G2))

I have attached an update to your workbook.

Let us know if this works for you.