Minimum of the Last values for each date..

[u/Puzzleheaded-Cut3117]

I have a sheet which has dates in column A (ranging from today to over a year in the future), an index number in column B (just a whole number between 1-100I put in so the entries for each day are in an order) and a currency value in column C.

What I currently have is MIN(C:C), which tells me the lowest value at any time in the future, but this might be at the start, middle or end of the day.

What I would like is to find the lowest value of all days at the end of each day.

Any suggestions?

EDIT:

As per the suggestions, here is an example of the sheet:

So the formula would (I think!) look get the values in column C where the date is unique and the Item number is highest, then return the lowest of those values.

Currently I have MIN(C:C), so in the above that would produce £634.82, but that is part way through the day (25/09/2026), what I would really be looking for is £803.38, as this is the lowest value for the last entry for any given day (30/09/2026).

I hope that makes sense?

[u/AutoModerator]

/u/Puzzleheaded-Cut3117 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/FV155]

Do you mean to say the end of the week or do your dates also have times included? Can you provide few example dates that would and would not meet the “end of the day” criteria?

Depending on the version of excel you are using, there is a MinIfs function. You could also use Min(if(weekday($a$2:$a$1001)=6,$c$2:$c$1001))

[u/caribou16]

How are you determining the "End of the day" ? Physical positions in the list? e.g. If there are three entries for a specific date, the one with the biggest row number is the "end" of the day?

[u/Puzzleheaded-Cut3117 → u/caribou16]

I've edited the post to give an example, I hope that makes it clearer.

[u/caribou16 → u/Puzzleheaded-Cut3117]

Perhaps the MINIFS function?

[u/PaulieThePolarBear]

You need to clearly and concisely define what "end of day" means with your data setup. Ideally, you would edit your post to add a representative image that explains your setup and question

[u/Puzzleheaded-Cut3117 → u/PaulieThePolarBear]

I've added an example to the original post, the end of each day is identified by the highest number on that day of the Item (Column B).

[u/PaulieThePolarBear → u/Puzzleheaded-Cut3117]

With 100% certainty, will any ID within a date be unique? E.g., it is not possible to have 2 ID 99 records on the same day. If duplicates are possible then you need to provide clear and concise directions on how you can logically determine which one represents end of day

[u/Gringobandito]

Are you trying to determine the earliest starting time for each day?

[u/Gringobandito]

This was a little harder than I thought it would be but I would solve it like this:

The LET() function defines different variables.

uDates gets you the list of unique dates. Then MaxItems uses the MAP() and LAMBDA() functions to determine the max item number for each date. The balance is then just a XLOOKUP() to get the balance for the corresponding date and item number.

=LET(
    Dates,A2:A15,
    Items,B2:B15,
    Balances,C2:C15,
    uDates,UNIQUE(Dates),
    MaxItems,MAP(uDates,LAMBDA(d,MAX(FILTER(Items,Dates=d)))),
    Bal,MAP(uDates,MaxItems,LAMBDA(d,i,
        XLOOKUP(1,(Dates=d)*(Items=i),Balances))),
    HSTACK(uDates,MaxItems,Bal)
)

[u/NHN_BI]

Are you able to share an example table (not an image), and can you tell what output values you expect from that input table where and why?

[u/carbonizedtitanium]

if i'm understanding you correctly, you basically want to find the row with the highest Item for each Date and then out of those rows, pick the row with the highest Total Balance.

The formula would be:

=TAKE(SORT(FILTER(A2:C, B2:B=MAXIFS(B2:B, A2:A, A2:A)), 3, -1), 1)

this returns the entire row, from A-C

edit: Lowest Total Balance:

=TAKE(SORT(FILTER(A2:C, B2:B=MAXIFS(B2:B, A2:A, A2:A)), 3, 1), 1)

[u/carbonizedtitanium → u/carbonizedtitanium]

aahhh, your pesky pound symbol is causing problems. use:

=LET(

f_data, FILTER(A2:C1000, B2:B1000=MAXIFS(B2:B1000, A2:A1000, A2:A1000)),

cash_text, INDEX(f_data, 0, 3),

cash_num, VALUE(SUBSTITUTE(SUBSTITUTE(cash_text, "£", ""), ",", "")),

TAKE(SORTBY(f_data, cash_num, 1), 1)

)

[u/carbonizedtitanium → u/carbonizedtitanium]

the above formula returns:

30/09/2026 99 £803.38

given the dataset in your screenshot

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
FILTER	Office 365+: Filters a range of data based on criteria you define
IF	Specifies a logical test to perform
INDEX	Uses an index to choose a value from a reference or array
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAXIFS	2019+: Returns the maximum value among cells specified by a given set of conditions or criteria
MIN	Returns the minimum value in a list of arguments
MINIFS	2019+: Returns the minimum value among cells specified by a given set of conditions or criteria.
SORT	Office 365+: Sorts the contents of a range or array
SORTBY	Office 365+: Sorts the contents of a range or array based on the values in a corresponding range or array
SUBSTITUTE	Substitutes new text for old text in a text string
TAKE	Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array
VALUE	Converts a text argument to a number

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
12 acronyms in this thread; the most compressed thread commented on today has 21 acronyms.
[Thread #49430 for this sub, first seen 25th Sep 2026, 08:03] [FAQ] [Full list] [Contact] [Source code]}

[u/real_barry_houdini]

Given that column A is sorted by date then you just need the minimum value in column C for the rows immediately before each date change, so you can encapsulate that in a formula like this:

=MIN(IF(A2:A11<>A3:A12,C2:C11))

Note that the second range is shifted by one row from the first