Get Last 5 in Column, Then Add, Average & Display. Six Amazing Examples. Excel Magic Trick 1650.
Вставка
- Опубліковано 15 вер 2024
- Download Excel File: excelisfun.net...
Learn how to get the last 5 numbers in a column and then add, average and display the last 5 items. Learn about a simple solution to this common task using the INDEX and ROWS functions and the Excel Table feature. See Six Examples. See the Excel Functions: INDEX, ROWS, SUM, AVERAGE, IF, FILTER and ROW.
1. (00:01) Introduction.
2. (00:24) Create Dynamic Range using INDEX and ROWS function with an Excel Table.
3. (01:28) SUM to add the dynamic range.
4. (01:44) AVERAGE to average the dynamic range.
5. (01:57) Non-Office 365 solution to display last five values.
6. (02:45) When you should NOT use IFERROR Function.
7. (03:30) Office 365 New Calculation Engine solution to display last five values.
8. (03:54) Formula for extracting last five text items.
9. (04:13) FILTER Function solution (not the best option).
10. (04:32) Summary and End Video Links.
Topics:
1. (00:01) Introduction.
2. (00:24) Create Dynamic Range using INDEX and ROWS function with an Excel Table.
3. (01:28) SUM to add the dynamic range.
4. (01:44) AVERAGE to average the dynamic range.
5. (01:57) Non-Office 365 solution to display last five values.
6. (02:45) When you should NOT use IFERROR Function.
7. (03:30) Office 365 New Calculation Engine solution to display last five values.
8. (03:54) Formula for extracting last five text items.
9. (04:13) FILTER Function solution (not the best option).
10. (04:32) Summary and End Video Links.
Another clear and concise video. Thank you for showing those of us who don not have O360 how to accomplish the same task. Please continue to include us.
I will : ) Office 365 IS the future, but the majority of Excel users are not there yet! I got your back : )
Very nice Mike!!! Came out with this
=INDEX(fSales[Numbers],SEQUENCE(D3,,COUNTA(fSales[Numbers])-D3+1))
Very cool. I wonder if INDEX SEQUENCE or INDEX:INDEX is faster ?
@@excelisfun If there is somebody to answer this question you are the one. :-))). I presume INDEX:INDEX. There is an add in for this calc, I do not have it. The LET functions should be timed also, using variables could have the benefits of helper columns.
The new calculation engine along with Dynamic Arrays are so powerful. I foresee many more awesome videos to come. Thanks Mike for sharing your knowledge.
Yes, I agree, all about 3000 videos will have to be re-done ; )
Thanks Mike, getting 'last numbers', especially when it has to be dynamic, has always been a struggle for a lot of people, this explains it clearly.
It is a very common task.ROWS and INDEX make it less of a struggle : )
Waooo...... Excellent video.
Glad you like it, Ashok!!!
A longer formula but is one cell formula that puts on the first row the total
=CHOOSE(INDEX(SEQUENCE(D3+1,,2,0)-MUNIT(D3+1),,1),"Tot last "&D3&" = "&SUM(INDEX(fSales[Numbers],SEQUENCE(D3,,COUNTA(fSales[Numbers])-D3+1))),INDEX(fSales[Numbers],SEQUENCE(D3+1,,COUNTA(fSales[Numbers])-D3)))
Could be set also to put more data on the same array formula,like the average on second cell, or at the end, manipulating CHOOSE and MUNIT a lot can be done, for the benefit of only one cell. With LET could be much easier to understand and read. If is interest I can write it down. :-)
Cool, cr gr0912 : ) : )
Excellent Mike....I used your old formula when I was tracking the last five results of teams in a league. Thanks for the update!
You are welcome, Roberto : )
New dynamic formulas... :) I love them :)
Thanks Mike, and thanks for contributing to the situation at the moment, with your videos. :) :)
You are welcome, John Borg : )
Dang..epic...thanks again. Then I saw Bill's LET example. Another cool variation. Wow...you guys rock
Just use Szysz's formula without LET, and it is even better!!!!!
Thanks for the video!
That's amazing Mike. The beauty with Excel is that, there are so many different ways of doing the same thing. One can also use combination of SUM, OFFSET and COUNT, something like =SUM(OFFSET(B2,COUNT(B3:B22),,-D3,1)), same for average. For displaying last 5 I've this: =IF(ROWS($F$6:F6)>$D$3,"",INDEX(B3:B22,COUNT($B$2:B22)-$D$3+1)). Table mouse handle is not working, hence given the range. Thanks & Cheers :)
Thanks for your formula contributions, Sachin!!!!!
That was very impressive Mike, thank you!
You are welcome, Chris : ) : )
Thanks a lot
Boom Boom Index Formula.Amazing
Thank you Mike
I try to solve
=IFERROR(INDEX($B$3:$B$23;COUNTA($B$3:$B$23)-$D$3+ROWS($D$3:D3));"")
=SUM(INDEX(B3:B23;COUNT(B3:B23)-D3+1):INDEX(B3:B23;COUNT(B3:B23)))
=AVERAGE(INDEX(B3:B23;COUNT(B3:B23)-D3+1):INDEX(B3:B23;COUNT(B3:B23)))
=INDIRECT(ADDRESS(ROWS($B$3:$B$23)-$D$3+ROWS($B$1:B3);2))
=SUM(INDIRECT(ADDRESS(ROWS($B$3:$B$23)-$D$3+ROWS($B$1:B3);2)):INDIRECT(ADDRESS(ROWS($B$3:$B$23)+ROWS($B$1:B3);2)))
=AVERAGE(INDIRECT(ADDRESS(ROWS($B$3:$B$23)-$D$3+ROWS($B$1:B3);2)):INDIRECT(ADDRESS(ROWS($B$3:$B$23)+ROWS($B$1:B3);2)))
=IF(ROWS($A$1:A1)>$D$3;"";OFFSET($B$3;ROWS($B$3:$B$23)-$D$3+ROWS($A$1:A1)-1;;$D$3))
=SUM(OFFSET(B3;ROWS(B3:B23)-D3;;D3))
=AVERAGE(OFFSET(B3;ROWS(B3:B23)-D3;;D3))
Lots of great formulas, except for IFERROR : (
Amazing Mike!
Glad you like it, Ed : )
I need to buy office 365 really cause these stuff are fun
Nice.👍👍👍
You are really awesome Mike.. nothing more to say..:-)
Thanks, Manoj!!!!
Fantastic!
Glad you like it, bevon!
Your videos are almost mesmerizing!
That should be a good thing. I Think?
ExcelIsFun I think it is! 🙂
You're a boss! Thank you so much
You are welcome so much, D H : ) : ) Thanks for the question!!!
Nice work!
Thanks Mike :-))
From me, LET of course (my new favorite function)
=LET(rng,fSalesAnswer[Numbers],INDEX(rng,SEQUENCE(D3,,ROWS(rng)-D3+1)))
Yes, LET is delightful! Thanks for the elegant solution, O Poet of Excel and PQ : )
BTW, Masterful One, your formula is even better without the LET : ) : ) : ) : )
Thank You Sir for the Great Video , We Can Use Latest Function i.e. Let Expression in Office insider
You are welcome, Janardhan!!! LET... soon : )
Is there a reason why you used INDEX’s instead of OFFSET? Is there an advantage to using one over the other?
Yes, big1975E, OFFSET is a volatile function, which means it has to recalculate the result any time you do anything, hit enter, insert a column and so on. This does not matter if the model is small. But often, people build models that have many formulas and span many sheets, then functions like OFFSET can really slow things down. That is why I try to teach methods that will be more efficient.
That makes sense. Thank you! I would love to see your solution for a complex TEXTJOIN formula that creates a list in a single cell of totals for the last three years where the year and total are separated by a hyphen and each year is on a separate line in the cell. I have a formula but it calculates really slowly.
Hello, In this video you use a Named Range called fSalesAnswer that auto increments whenever a row is added. Do you sell a document that I can learn how to create that function or Macro? Thanks!
This is Amazing
Amazing
Glad it is amazing for you, Kiruna!!!
How do you do for rows ?
What to do to get the last five based on a criteria from another column (variable)?
how can i use this for last 5 in rows, please reply
How do i represent the result from the formulas horizontally in a row ..... not vertically in a column like in the above example ,say last 5 results for a team in a tabular format where the results are tabulated in a vertical format , whereas in the output id like it to be represented horizontally
Thank you Mike for sharing. Does the index(rows()) formula work for pivot table as well? It does not seem to be dynamic as I have to manually select the rows every time the row number changes.
I have not tried that. It sounds like from your test, it does not work.
@@excelisfunOk. Thank you Mike.
This is one cell array formula that sets above the last rows extracted, the SUM and the AVERAGE. The formula is:
=LET(
lst,D3,slnr,fSales[Numbers],
trw,COUNTA(slnr),
inrw,SEQUENCE(lst,,trw-lst+1),
in,INDEX(slnr,inrw),
ch,SEQUENCE(D3+2,,3,0)-INDEX(MUNIT(D3+2)*2,,1)-INDEX(MUNIT(D3+2),,2),
arry,CHOOSE(ch,"Tot lst "&lst&" = "&ROUND(SUM(in),2),"Avrg lst "&lst&" = "&ROUND(AVERAGE(in),2),INDEX(slnr,SEQUENCE(lst+2,,trw-lst-1))),
arry
)
The variables we use are :
lst as value in cell D3,slnr as the sales number table itself,
trw as total rows of the table (no blanks or text scenario, could be modified to cover the extraction if there are blanks or text),
inrw as the array of the last rows used in INDEX extraction,
in as the INDEX itself extracted,
ch as the array that we need for the CHOOSE index argument, creates an array like this {1;2;3;3;...} that should be dynamic to have as many "3" as we have last rows,
arry as our result array, the CHOOSE itself with value 1 argument as the SUM, value 2 as AVERAGE, and the bunch of values 3 as the INDEX.
Note: for this to work, INDEX used inside CHOOSE is 2 rows taller.
I can not imagine the size of this formula without LET. The benefit is HUGE. For anybody to use it, you only need the table created, input the cell reference for the last rows, input the table name, copy, paste, and you are good to go. Everything else is contained in the formula.
LET is very amazing, as is your formula cr gr0912!!!!!
2:34 Condition If
Yes, we do not want IFERROR ; )
@@excelisfun Yes !
Niceeeee
Thaaaaanks : )
We might also use =IF(ROWS($G$6:G6)
Cool : )
Hopefully you see this…. When I put this in I keep getting zero HELP