Ask Your Question
0

How do I select a date range in an array?

asked 2018-12-24 17:22:57 +0200

ky gravatar image

Hello folks!

I've an array where each row is a day of the year (Jan 1 - Dec 31) and columns are the year.

Cells are populated with text only (cities names).

How do I know e.g. in the last 12-months period starting from today (today date should be updated every file opening) how many of those cells are populated?

A non blank cell count basically but in a specific date range.

Is there someone that can help me with array formulas?

Merry XMas!

edit retag flag offensive close merge delete

Comments

This is an interesting question. But, unfortunately, you will not be able to get an exact answer without very complex calculations. The day of February 29 is to blame; its presence or absence in the year will distort the result of the formula. And to take into account the leap year, the formula will have to be very complicated. You should either come to terms with the fact that the result will differ slightly from the true value, or change the table with cities and dates so as to avoid appearing on February 29 in a non-leap year and not to miss this day in a leap year.

JohnSUN gravatar imageJohnSUN ( 2018-12-25 09:18:06 +0200 )edit

My first thought was the same, but now I think that 29.02 brings no any complications to calculations if you consider "last 12-months" or "during last year" period as a period between two same dates in two consecutive years (inclusive or exclusive first or last day of the period, just need to define it before and keep it in mind after), but not as 365/366 days before today (which will prompt you to find, if this period shall include 29.02 or not). If I understand author correctly, first column is all possible dates in year, so 29.02 is included as the possible option, but it stays empty almost forever :) And the same date is always on the same row. So one just needs to count populated cells below the current row in the previous column and above the current row in the current column. So during ...(more)

SM_Riga gravatar imageSM_Riga ( 2018-12-25 13:58:13 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2018-12-25 15:14:19 +0200

JohnSUN gravatar image

updated 2018-12-25 18:58:29 +0200

If you promise that on February 29, 2018 you was not go anywhere, then let's count the cities.

Naming convention: To simplify the formula a bit, let's assign names to the work ranges, as shown in this picture.

NamedRangesForComplexFormula.png

The first step is very simple - to find the offset value from the column days to the desired year. For last year and this year it will be very similar, differ only by -1:

MATCH(YEAR(TODAY())-1;YearsRow;0)
MATCH(YEAR(TODAY());YearsRow;0)

Finding the string with the current date will be not much more difficult:

MATCH(TEXT(TODAY();"MMM D");DaysColumn;0)

Now you can easily determine the ranges for counting - take the whole column of names of days, move right to the desired year and cut off the excess:

OFFSET(DaysColumn;MATCH(TEXT(TODAY();"MMM D");DaysColumn;0);MATCH(YEAR(TODAY())-1;YearsRow;0);ROWS(DaysColumn);1)
OFFSET(DaysColumn;0;MATCH(YEAR(TODAY());YearsRow;0);MATCH(TEXT(TODAY();"MMM D");DaysColumn;0);1)

Please note: it is assumed that there are no other data below the table of cities. Otherwise, the first formula will be a little more complicated - to limit the height of the OFFSET() range, you need to once again calculate the position of the current date using the MATCH()

Now, using the usual COUNTA(), count the number of non-empty cells in each of the subranges and sum up the values ​​obtained

=COUNTA(
    OFFSET(DaysColumn;
         MATCH(TEXT(TODAY();"MMM D"); DaysColumn;0); 
         MATCH(YEAR(TODAY())-1;YearsRow;0); 
         ROWS(DaysColumn);1))
+ COUNTA(
    OFFSET(DaysColumn;
         0;
         MATCH(YEAR(TODAY());YearsRow;0);
         MATCH(TEXT(TODAY();"MMM D");DaysColumn;0);1))

Edit by @SM_Riga on 25.12.2018 19:55 with the permission of @JohnSUN

Please also find Demo spreadsheet with above mentioned functionality and also with how to make formulas easier demonstration, based on the different data structure.

edit flag offensive delete link more

Comments

With the permission of @JohnSUN I will add demo spreadsheet to his answer which demonstrates a bit this functionality and also shows how much easier is make same calculations if data is arranged consecutive in one column. Made it for this topic, but there is already no need to make a new answer :)

SM_Riga gravatar imageSM_Riga ( 2018-12-25 15:42:13 +0200 )edit

@SM_Riga Thank you! And Happy New Year!

JohnSUN gravatar imageJohnSUN ( 2018-12-25 15:52:25 +0200 )edit

@JohnSUN Just added file promised. Thanks a lot and Merry Christmas and Happy New Year for you too!

SM_Riga gravatar imageSM_Riga ( 2018-12-25 19:00:58 +0200 )edit
0

answered 2018-12-25 14:08:40 +0200

ky gravatar image

@ SM_Riga You're correct on that: leap year has to be considered and 29.02 isn't to blame since it gonna be empty in non leap years. First column could be any possible year, and yes I need just to count the populated cells.

Sounds a bit complicated in a spreadsheet and I evaluated also a possible solution using a database that gives me more flexibility on queries, but that adds some other complications (not related to the database but the environment).

Thanks.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-12-24 17:22:57 +0200

Seen: 65 times

Last updated: Dec 25 '18