Ask Your Question

How can I find the latest date in a non-contiguous range (e.g. C5, C10, C15...) that is also earlier than the date in another cell (e.g. A1)?

asked 2019-01-20 21:20:13 +0200

Progresser gravatar image

The range is not simple, such as C5:C50, but each date is the first in a set of entries. I want to find the latest date in each column of entries which is no later than (say) the last date of a particular month.

edit retag flag offensive close merge delete


-1- The sheet seems to be badly designed (every-fifth-row-design).
-2- The subject and the text of the question are inconsistent.

Lupp gravatar imageLupp ( 2019-01-20 22:28:19 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-06-23 19:43:22 +0200

Progresser gravatar image

The way I've achieved it, using Mario's model, is (in C5, and similarly in D5, E5, etc.):

As an array formula, until SUMPRODUCT works on them, this returns an array of valid entries, from which MAX chooses the greatest.
Cell BT2 holds the date I enter to use as the last day of the month I want to restrict entries for.
I can thus get a snapshot in C5 of what the account balance was on the latest date in that range up to then - also in D5, E5, etc..

To explain the sections within the IF function for anyone who's interested in my version of Mario's answer:
C$15:C$1285 - this (in the array formula) returns the whole range of entries within which I'm searching.
ISNUMBER(C$15:C$1285) - checks that we're not looking at a text or blank entry.
. . . . If it were, then IF returns 0, the last number before the ))}. Otherwise...
(C$15:C$1285<=$BT$2) - this returns 1 (TRUE) for each cell in that range where it is no later than the cutoff date in BT2, else 0 (FALSE).
(C$15:C$1285>0) - this returns 1 (TRUE) for each cell >0, but 0 (FALSE) if the cell is zero or negative.

. . . . and the master stroke:
(MOD(ROW(C$15:C$1285);5)=0) - restricts the array results to every 5th cell in the range returned by the array formula, which hold dates
. . . . i.e. C15, C20, C25, etc.
MAX gets the latest of these array results,
and SUMPRODUCT means that for each cell the sections above are multiplied together and added.
Any FALSE test result 0 multiplied by the other sections will give 0, and hence exclude that cell from the array sum.

I don't understand why SUMPRODUCT and MAX aren't the other way round:
. . . . . . . . . . {=MAX(SUMPRODUCT(IF....
. . . . but I accept that when I tried it that way round it didn't work; I got #VALUE!

edit flag offensive delete link more

answered 2019-01-20 22:29:09 +0200

Lupp gravatar image

updated 2019-01-20 22:46:28 +0200

See this C:\fakepath\ask180052strange_1.ods attachment.

Restricting the range (in the example C:C) more strictly would reduce the recalculation time.
Asking for a result depending on the values from scattered cells should never be needed in well designed sheets. If the respective cells are scattered in a less regular way problems increase.

edit flag offensive delete link more


Could it be more simple with a formula like =MAXIFS(E:E;E:E;"<"&A2;E:E;">0") if there are not intermediate cells with dates or number values or to do it every a given number of rows =SUMPRODUCT(MAX(IF(ISNUMBER(E:E);E:E*(E:E<A2)*(E:E>0)*(MOD(ROW(E:E);5)=0));0))

m.a.riosv gravatar imagem.a.riosv ( 2019-01-21 00:15:06 +0200 )edit

Thank you, Lupp. Your attachment contains some great ideas, though I'll need to look at your formulas in more detail as they're quite complicated. The basic idea looks good, though. When I've tried it out I'll comment again.

Thank you too, Mario SV (?), that last suggestion also looks like a good solution, and probably easier. I'll give it a try and let you know how it goes.

All the best, Progresser.

Progresser gravatar imageProgresser ( 2019-01-22 12:39:35 +0200 )edit

Thanks a lot, Mario! Your solution works fine, and I'm using it now and every day. I've also learned a lot from how you constructed it, and hope to be able to use some of the techniques elsewhere.

Progresser gravatar imageProgresser ( 2019-06-23 17:15:08 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-20 21:20:13 +0200

Seen: 84 times

Last updated: Jun 23