Skip to main content
Web Applications

Return to Question

Bumped by Community user
Post Reopened by Blindspots♦
Post Undeleted by Blindspots♦
The "invented" function was causing confusion. But looking at the example and the prior efforts using MATCH/LOOKUP makes it unlikely this is a custom function question..
Added to review
Source Link
Blindspots ♦
Blindspots ♦
  • 7.5k
  • 5
  • 20
  • 38

Special function Formula to calculate difference in Google Sheetrows between the current row and the first row above whose value matches a condition

I can't come up with a combination of functions (MATCH,LOOKUP,etc.) in Google Sheets that would return the row numberoffset of thea cell from another in the same column that matches thea given rule in the given range. Moreover,

In the image below is an example using an invented function I need to find this value from bottomnamed "FIRST_VALUE" to top.illustrate what I mean:

  1. FIRST_VALUE takes two inputs:
    1. A row number:
      ROW() is used in the example, the current row 61
    2. A rule:
      In the example it is ">655"
  2. My invented function FIRST_VALUE would return 5 because the value 655,79 is the first value in a row above row 61 that is greater than 655 and it is found in row 56 which is 5 rows above.

I am attaching an image in which I show the invented function "FIRST_VALUE"have tried combinations of MATCH, which in this particular case should return the value "5"LOOKUP, because just 5 rows above is the value 655etc.79, which is higher than the rule ">655"but none have worked.

enter image description hereexample showing 'invented function' detailed above

This question has also been posted on the Google community help forum: Special function in Google Sheet

Special function in Google Sheet

I can't come up with a combination of functions (MATCH,LOOKUP,etc.) in Google Sheets that would return the row number of the cell that matches the given rule in the given range. Moreover, I need to find this value from bottom to top.

I am attaching an image in which I show the invented function "FIRST_VALUE", which in this particular case should return the value "5", because just 5 rows above is the value 655.79, which is higher than the rule ">655".

enter image description here

Formula to calculate difference in rows between the current row and the first row above whose value matches a condition

I can't come up with a combination of functions in Google Sheets that would return the row offset of a cell from another in the same column that matches a given rule.

In the image below is an example using an invented function I named "FIRST_VALUE" to illustrate what I mean:

  1. FIRST_VALUE takes two inputs:
    1. A row number:
      ROW() is used in the example, the current row 61
    2. A rule:
      In the example it is ">655"
  2. My invented function FIRST_VALUE would return 5 because the value 655,79 is the first value in a row above row 61 that is greater than 655 and it is found in row 56 which is 5 rows above.

I have tried combinations of MATCH, LOOKUP, etc., but none have worked.

example showing 'invented function' detailed above

This question has also been posted on the Google community help forum: Special function in Google Sheet

Post Deleted by CommunityBot
Post Closed as "Needs details or clarity" by Rubén - Volunteer Moderator -♦
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
deleted 42 characters in body
Source Link
serenesat
serenesat
  • 10k
  • 30
  • 33
  • 50

please advise.

I can't come up with a combination of functions (MATCH,LOOKUP,etc.) in Google Sheets that would return the row number of the cell that matches the given rule in the given range. Moreover, I need to find this value from bottom to top.

I am attaching an image in which I show the invented function "FIRST_VALUE", which in this particular case should return the value "5", because just 5 rows above is the value 655.79, which is higher than the rule ">655".

Thank you very much!

enter image description here

please advise.

I can't come up with a combination of functions (MATCH,LOOKUP,etc.) in Google Sheets that would return the row number of the cell that matches the given rule in the given range. Moreover, I need to find this value from bottom to top.

I am attaching an image in which I show the invented function "FIRST_VALUE", which in this particular case should return the value "5", because just 5 rows above is the value 655.79, which is higher than the rule ">655".

Thank you very much!

enter image description here

I can't come up with a combination of functions (MATCH,LOOKUP,etc.) in Google Sheets that would return the row number of the cell that matches the given rule in the given range. Moreover, I need to find this value from bottom to top.

I am attaching an image in which I show the invented function "FIRST_VALUE", which in this particular case should return the value "5", because just 5 rows above is the value 655.79, which is higher than the rule ">655".

enter image description here

Source Link
Petr Valeš
Petr Valeš
  • 1

Special function in Google Sheet

please advise.

I can't come up with a combination of functions (MATCH,LOOKUP,etc.) in Google Sheets that would return the row number of the cell that matches the given rule in the given range. Moreover, I need to find this value from bottom to top.

I am attaching an image in which I show the invented function "FIRST_VALUE", which in this particular case should return the value "5", because just 5 rows above is the value 655.79, which is higher than the rule ">655".

Thank you very much!

enter image description here