I currently have this formula in B22: =IF(ISBLANK(G22),"",IF(A22="SPEED",0,(86400*(G22-(G3)))))

But I don't want G3 to be a fixed point in the equation. I want it to be a variable. The variable it should be is: This is the cell in column G that is adjacent to its counterpart cell in column A that contains the string "SPEED" that is also physically closest to the target row (in this case, row 22).

The the logic should be:

• If the cell in G22 is blank,

  • If true, return null.

    If false, run the following formula:

    • • If the cell in A22 equals "SPEED",

        • If true, return 0.

        • If false, then return the following formula:

          • Multiply 86400 by the sum of G22 adjacent minus G[Row Number], where [Row Number]=The row closest to G22 where value="SPEED" from A22:$A$3)

I can currently get the rest of the formula in place, but I don't know how to write for that variable.

CodePudding user response：

If you are willing to provide the sample data, it will make easier for us to understand what you want to get.

You can use ADDRESS to return these
G[Row Number]; where [Row Number]=The row closest to G22 where value="SPEED" from A22:$A$3). Into variable; and INDIRECT it to get the value.

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

and

INDIRECT(ref_text, [a1])

The complex one is that you want to get the value of closest ROW (in this case, row 22) which have SPEED value (which is a text), you cannot use MIN() or MAX() formula to approach or manipulate it. I suggest to use MATCH function and set the [match_type] as "1" to find the largest array value.

MATCH(lookup_value, lookup_array, [match_type])

the formula may looks like this, use "1" on the last syntax :

MATCH("SPEED";$A$1:$A$20;1)

And, Lets see if this complete formula work on your target :

=IF(ISBLANK(G22);"";IF(A22="SPEED";"0";(86400*(G22-(INDIRECT(ADDRESS(MATCH("SPEED";$A$3:$A$22;1);7;1)))))))