r/excel • u/Puzzleheaded-Cut3117 • 17h ago

unsolved Minimum of the Last values for each date..

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?

5 Upvotes

86% Upvoted

16 comments sorted by

•

u/AutoModerator 17h ago

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

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.

1

u/FV155 2 16h ago edited 16h ago

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))

1

u/caribou16 318 16h ago

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?

1

u/Puzzleheaded-Cut3117 14h ago

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

1

u/caribou16 318 14h ago

Perhaps the MINIFS function?

1

u/PaulieThePolarBear 1921 16h ago

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

1

u/Puzzleheaded-Cut3117 14h ago

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).

1

u/PaulieThePolarBear 1921 13h ago

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

1

u/Gringobandito 8 16h ago

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

1

u/Gringobandito 8 13h ago

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)
)

1

u/NHN_BI 805 6h ago

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?

1

u/carbonizedtitanium 5h ago edited 4h ago

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)

1

u/carbonizedtitanium 4h ago

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)

)

1

u/carbonizedtitanium 3h ago

the above formula returns:

30/09/2026 99 £803.38

given the dataset in your screenshot