How to select a row containing today's date?

[UnionJack]

My sheet has a row of personal numeric data for each day, with the dates in B3:B114. As time goes by I extend the sheet by adding blocks of rows. I want to find the average of the entries in column S3:S114, but only up to today’s date. Zero values can appear within that range and should contribute to the average.

At the moment I have the simple formula =AVERAGE(S3:S114), but what should I put in place of S114 to limit the calculation to the days up to the present? I assume I need a ROW() function and a date comparison, but I can’t see how to select the one row that represents today.

[anon73440385]

Hello,

use: =AVERAGE(INDIRECT("S3:S" & MATCH(TODAY();B:B;0)))

Note(s):

• Your dates in column B must be real calc dates (i.e. integers) and must not be text.
• The first row containing date of TODAY() is taken into account.
• If you use range B3:B114 to find the matching row for today’s date, you need to use: =AVERAGE(INDIRECT("S3:S" & MATCH(TODAY();B3:B114;0)+2))

Tested using LibreOffice:

Version: 6.4.1.2, Build ID: 4d224e95b98b138af42a64d84056446d09082932
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kf5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded

Hope that helps.

If the answer helped to solve your problem, please click the check mark () next to the answer.

[UnionJack]

That did it! Many thanks. I’d never have arrived at that on my own.