# Get largest value from a list with at least 2 entries

1. ## Get largest value from a list with at least 2 entries

I have table with consecutive counting months and days and I use the LINEST function to calculate the linear regression of the latest month but it doesn't work very well when a month have only one entry. This is the reason why I'm looking for a way to get the largest value from a list (the months) but only if it has at least two points (two enrties).
Here something more concrete, the table looks like something like this one:
 Month Day Value 2 33 20 2 45 24 2 54 27 3 65 30 3 78 35 3 87 39 4 92 45

I use the LINEST function with the FILTER function to calculate the linear regression of the last month. ``Please Login or Register  to view this content.``
With this formula the LINEST function returns for the 4th month the values 0 and 45, what actually a quite useless linear regression is.
In this case I would like to have the linear regression still starting from the 3rd month although there is already a entry in the 4th month.

What I'm looking for is to replace the ``Please Login or Register  to view this content.``
with a formula which returns 3 instead of 4 if there is only one entry for the largest value.  Register To Reply

2. ## Re: Get largest value from a list with at least 2 entries

If you are using FILTER then you are no longer using Excel 2016 - what are you using now? Please update your profile.  Register To Reply

3. ## Re: Get largest value from a list with at least 2 entries

Can you post an excelfile instead of an formula ?
It'll be a compounded "aggregate"-formula  Register To Reply

4. ## Re: Get largest value from a list with at least 2 entries

So nobody has an idea how to achieve it?  Register To Reply

5. ## Re: Get largest value from a list with at least 2 entries

We've been waiting for you to confirm your Excel version!

Now that you have, can you please tell us the results (values) that you would want returning in the sample workbook?

A also asked you if this question is substantially different to your prevous thread - you need to respond to a moderator's questions, please.  Register To Reply

6. ## Re: Get largest value from a list with at least 2 entries

I guest what you want is

=LINEST(BYROW(UNIQUE(Table[Month]),LAMBDA(x,MAX((Table[Month]=x)*Table[Value]))),BYROW(UNIQUE(Table[Month]),LAMBDA(x,MAX((Table[Month]=x)*Table[Day]))))  Register To Reply

7. ## Re: Get largest value from a list with at least 2 entries

Well I changed my Excel version to 365. Anyway I found another way to achieve what I wanted, I just get the month of the 3rd last row of the table ``Please Login or Register  to view this content.``  Register To Reply

8. ## Re: Get largest value from a list with at least 2 entries

OK - thanks for letting us know.  Register To Reply

9. ## Re: Get largest value from a list with at least 2 entries Originally Posted by windknife I guest what you want is

=LINEST(BYROW(UNIQUE(Table[Month]),LAMBDA(x,MAX((Table[Month]=x)*Table[Value]))),BYROW(UNIQUE(Table[Month]),LAMBDA(x,MAX((Table[Month]=x)*Table[Day]))))
Thanks for the reply but it is not what I was looking for. I tested the formula and it returns only the last entry of each month.

 BYROW(UNIQUE(Table[Month]),LAMBDA(x,MAX((Table[Month]=x)*Table[Value]))) BYROW(UNIQUE(Table[Month]),LAMBDA(x,MAX((Table[Month]=x)*Table[Day]))) 27 54 39 87 45 92

My objective was to have a meaningful linear regression of the current month, but if the current month has only one point, it has to return the linear regression of the previous month + current month

In this example it should have used the month 3 and 4

 FILTER(Table[Value],Table[Month]>=INDEX(TABLE[Month],ROWS(TABLE)-3)) FILTER(Table[Day],Table[Month]>=INDEX(TABLE[Month],ROWS(TABLE)-3)) 30 65 35 78 39 87 45 92  Register To Reply

10. ## Re: Get largest value from a list with at least 2 entries ``Please Login or Register  to view this content.``
Edit : i modified my post !  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 