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

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

Hi, I have an excel worksheet where I need to pick up the values from 2 non-blank cells from Row 5, and the corresponding values from Row 1 to use in a formula: (see attached xl sheet). Here the cells B5 and D5 are non-blank. The value for the formula is returned in B6.
B1 + ((D1 - B1) * (10 - B5) / (D5 - B5))

2. ## Re: Nonblank values from a row and the corresponding values from another row to use in for

Maybe this:
Formula:
`Please Login or Register  to view this content.`

3. ## Re: Nonblank values from a row and the corresponding values from another row to use in for

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

4. ## Re: Nonblank values from a row and the corresponding values from another row to use in for

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)