Glad this video helps! Excel has changed a lot since I made this 15 years ago. If you have Microsoft 365 Excel, this is all you need now: =TAKE(SORT(FILTER(D13:E35,(YEAR(Date)=O12)*(TEXT(Date,"MMM")=P12)*(Product=Q12)),,-1),5)
@MrXceller , how about a PivotTable (Excel 2007 or 2010) and then use the top 10 filter? Otherwise, a helper column could be used perhaps. You might try posting to THE best Excel Question site: mrexcel [dot] com/forum
If your data is across the columns instead of down the rows, would you just change in the formula "Rows" to "Column" ? I need to know the column number for the index.
Sir, can you make any video index with match function to retrieve the value base on 3 or more criteria. Like name...........country..........job.........salary if we have four column which are filled with data.... so we can search the salary base on 3 entry...(name.......country......job)...thanks for nice video..
Could the sumproduct construct be used in this situation. I have a similar situation with 4,000 rows, 40 columns which I cannot array formulas, if I do it would crash the workbook. Is there a way to get around the array formula?
Great video! Thank you for this! One question: Could you use a data validation drop down list for the Named Ranges used in this formula? So as you can choose the year and the top 10 automatically adapts, I also would like to change my named ranges that are used in this formula by using a drop down list. If I choose a different Named range in the list, I want the formula to adapt automatically. But I don`t know if this is even possible?
Hey Professor. Love your videos. So well explained. I followed this video and got an odd result for what you used as sales reps to fill column K and account for multiples in Value. I put in the long formula, and when I do F9 to test result I get correct answer, but the answer in the cell is a different value completely from the list of again what you used as Sales Reps. Can you know what is happening. Been trying to fix 3 hrs a day for a week! Thanks.
Dreat video, I've used a very similar formular to find my web pages with the largest sales from certain categories (I havent created a table yet and named the columns though). =LARGE(IF(Master!B:B=A1,Master!F:F),1) A1 contains a drop down list of all of my categories and this is all working great but I also need to have an option to look at all pages to return the top 10 pages from all categories e.g. not having a selected category. I've tried a IF(OR( and an IF ELSE but neither of these seem to work. Could you explain how this could be acheived please? I guess it would be similar to if you wated to also have an option to view the top sales from all years not just an individual year but have the option to choose between either.
No. ROWS would yield 1, 2, 3, 4... as the formula is copied down. COUNTIF will yield 1 every time, except when there are duplicates, and then COUNTIF will yield 1, 2, 3...
Hi Sir, Could you help me. I need formula for search (Average Top 10 % if greater than 0) if i use =AVERAGE(LARGE(B1:B101,ROW(INDIRECT("1:10")))) then zero is include to average
NAME CLASS MARKS rohan V 40 mohan V 30 sohan VII 45 viru V 20 siru VII 55 tiru VII 65 miru V 60 hazel VII 0 raven V 0 topu VII 25 i want names who score very minumum marks except 0, with class Can u help me out thnx in adv.
I’ve been using Excel for 25 years. That’s the most amazing formula I’ve ever used. I also spent hours trying to figure this out. Truly Magic!!!
Glad this video helps! Excel has changed a lot since I made this 15 years ago. If you have Microsoft 365 Excel, this is all you need now:
=TAKE(SORT(FILTER(D13:E35,(YEAR(Date)=O12)*(TEXT(Date,"MMM")=P12)*(Product=Q12)),,-1),5)
Excellent thanks for making this video for excel users
awesome, I had been searching for hours to fix my problem, this has done the job nicely!
Nicely done!
You are welcome!
you are big help for excel people thank you
THANKS YOU SAVED MY LIFE
Very nice. Thanks a lot for sharing this
Thanks a lot, you Sir are always a great help.
You are welcome a lot, Delight In Life! Thanks for your support with your comment, Thumbs Up and Sub : )
How about we are comparing via column not rows? Appreciate the help in advance.
Can we not use AND function for these 3 conditions, instead of 3 IFs? Mike you help is needed on this. Thank you
@MrXceller , how about a PivotTable (Excel 2007 or 2010) and then use the top 10 filter? Otherwise, a helper column could be used perhaps. You might try posting to THE best Excel Question site:
mrexcel [dot] com/forum
If your data is across the columns instead of down the rows, would you just change in the formula "Rows" to "Column" ? I need to know the column number for the index.
Sir, can you make any video index with match function to retrieve the value base on 3 or more criteria. Like
name...........country..........job.........salary
if we have four column which are filled with data.... so we can search the salary base on 3 entry...(name.......country......job)...thanks for nice video..
In video 616, you use the small formula and also removed duplicates. Is there a way to combine the If/Index and also remove duplicates?
If you send me the link to your post at the Mr Excel Message Board, I can post a solution.
Thank you!
Could the sumproduct construct be used in this situation. I have a similar situation with 4,000 rows, 40 columns which I cannot array formulas, if I do it would crash the workbook. Is there a way to get around the array formula?
Great!
Great video! Thank you for this!
One question: Could you use a data validation drop down list for the Named Ranges used in this formula?
So as you can choose the year and the top 10 automatically adapts, I also would like to change my named ranges that are used in this formula by using a drop down list. If I choose a different Named range in the list, I want the formula to adapt automatically.
But I don`t know if this is even possible?
Hey Professor. Love your videos. So well explained. I followed this video and got an odd result for what you used as sales reps to fill column K and account for multiples in Value. I put in the long formula, and when I do F9 to test result I get correct answer, but the answer in the cell is a different value completely from the list of again what you used as Sales Reps. Can you know what is happening. Been trying to fix 3 hrs a day for a week! Thanks.
Thanx
You are welcome, Ali!!!!
Hey, I think array formula
=IFERROR(LARGE(--($I$12=Year)*--(Month=$J$12)*--(Product=$K$12)*(Sales),I15),"")
will be more efficient for this problem
Dreat video, I've used a very similar formular to find my web pages with the largest sales from certain categories (I havent created a table yet and named the columns though).
=LARGE(IF(Master!B:B=A1,Master!F:F),1)
A1 contains a drop down list of all of my categories and this is all working great but I also need to have an option to look at all pages to return the top 10 pages from all categories e.g. not having a selected category. I've tried a IF(OR( and an IF ELSE but neither of these seem to work. Could you explain how this could be acheived please? I guess it would be similar to if you wated to also have an option to view the top sales from all years not just an individual year but have the option to choose between either.
No. ROWS would yield 1, 2, 3, 4... as the formula is copied down. COUNTIF will yield 1 every time, except when there are duplicates, and then COUNTIF will yield 1, 2, 3...
Hi Sir, Could you help me. I need formula for search (Average Top 10 % if greater than 0)
if i use =AVERAGE(LARGE(B1:B101,ROW(INDIRECT("1:10")))) then zero is include to average
When writing a word, it does not appear as a name
Just a function ؟
Why did you use the small function?
NAME CLASS MARKS
rohan V 40
mohan V 30
sohan VII 45
viru V 20
siru VII 55
tiru VII 65
miru V 60
hazel VII 0
raven V 0
topu VII 25
i want names who score very minumum marks except 0, with class
Can u help me out
thnx in adv.