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)
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.
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...
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.
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
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.
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?
@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
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..
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.
![[Gegagedigedagedago] NEW Help Gegagedigedagedago Nugget escape from Nikocado Avocado Challenge](http://i.ytimg.com/vi/hJWnwRC2Fz8/mqdefault.jpg)
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!
How about we are comparing via column not rows? Appreciate the help in advance.
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.
Can we not use AND function for these 3 conditions, instead of 3 IFs? Mike you help is needed on this. Thank you
You are welcome!
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?
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...
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.
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
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.
Nicely done!
When writing a word, it does not appear as a name
Just a function ؟
Why did you use the small function?
If you send me the link to your post at the Mr Excel Message Board, I can post a solution.
you are big help for excel people thank you
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?
@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
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 : )
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..
Thanx
You are welcome, Ali!!!!
Very nice. Thanks a lot for sharing this
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
THANKS YOU SAVED MY LIFE
Great!
Thank you!
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.