0

I have an Excel file that tracks the maximum elevation of the sun throughout the year, one row per day, given a specific lat/lon. If I edit the lat/lon the daily maximum elevation changes.

I'd like to write a formula that queries the entire database (2 columns, 366 rows) and give me the day when the sun first begins reaching a given elevation, and then again when it no longer reaches that elevation.

For example, Honolulu will experience a max solar elevation angle of 80 degrees on May 1, remain at 80 degrees or higher (once daily) until Aug 19th when it drops below 80 degrees again.

I'd like an Excel function that generates these two dates, given that I already have the elevation data.

Thanks!

New contributor
Sebastian is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Please can you include mock-up sample data with expected output? – JvdV Aug 13 at 19:21
1

The solution here is to use a combination of INDEX() and MATCH() in an array formula.

I wrote two array formulas that solve the problem.

Start Date: {=INDEX(Dates, MATCH(TRUE, (Elevation>=Find_Elevation), 0), 1)}

End Date: {=INDEX(Dates, MATCH(TRUE, (Elevation>=Find_Elevation), 1), 1)}

Here is how they work

  • Elevation is a named range that holds every elevation for the year, Find_Elevation is a named range that is the the one we're looking for. In your example, this is 80
  • The expression (Elevation>=Find_Elevation) returns an array of TRUE and FALSE (which is why this needs to be an array formula). It returns TRUE whenever the elevation for each day is greater than or equal to the elevation you're looking for.
  • The MATCH() function with third argument 0 returns the index of the first time it sees TRUE. This is the index of the first day that the sun is above the elevation you're looking for.
  • Using third argument 1 in the MATCH() function has it return the last time it sees TRUE. This is the index of the last day the sun is above the elevation you're looking for.
  • We pass both of these as the second argument to INDEX(), and it returns the date corresponding to the value returned by MATCH()

I used FormulaChop to generate the formulas above (Full Disclosure: I wrote FormulaChop). Here is a screenshot of the how FormulaChop displays this formula. Here is a link to a spreadsheet that demonstrates the formula. Please excuse the fact that I had to make up the solar elevations.

New contributor
FormulaChop is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

Sebastian is a new contributor. Be nice, and check out our Code of Conduct.

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.