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.
-1- The sheet seems to be badly designed (every-fifth-row-design).
-2- The subject and the text of the question are inconsistent.
See this 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.
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))
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.
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.
The way I’ve achieved it, using Mario’s model, is (in C5, and similarly in D5, E5, etc.):
{=SUMPRODUCT(MAX(IF(ISNUMBER(C$15:C$1285);C$15:C$1285(C$15:C$1285<=$BT$2)(C$15:C$1285>0)*(MOD(ROW(C$15:C$1285);5)=0));0))}
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!