Mr Excel & excelisfun Trick 8: Top 10 Filter Trouble

[1022roland]

Mr. Excel, I would like to ask if the given is like this figure (shown in below), what would be the formula to be use? I tried this but shown #NUM! I really appreciate if you will answer my concern. thanks....

Name Data

Tom basketball

Tom baseball

Tom soccer

Fred volleyball

Fred football

Joe chess

Joe dark

[MrHelal123]

I did not even know what a cell was before i watched your videos. I look forward to your replies on the questions asked and once again, thank you so much. I dont know how to reward and thank you enough for your support but i really do mean it.

[ExcelIsFun]

Rate all video 5!

[MrHelal123]

Mike i know i have sent you a few mails, and questions in regards to dynamic index chart (to extract top 3 values rather than last 3 values) and a few other questions in regards to reverse 2 way look up (0, in the index argument for the row criteria etc)...I really appreciate your support and with your explanation and help, i am learning and becoming better with excel.

[MrHelal123]

Mike how in this video you showed how to extract the bottom scores. But what if there are duplicate of bottom scores, whats the formula to have it skip the duplicate scores and move to the next lowest score. i,e 1 2 2 3 4 5 i would want the bottom 5 scores to be 1,2,3,4,5 and not 1,2,2,3,4 (so basically skip the duplicate value of low scores)

[ExcelIsFun]

I don't know. You might have to post this question to the Mr Excel Message Board.

[gajukrish]

ok thank you very much, you'r really great for helping others.

[ExcelIsFun]

You are welcome!

[gajukrish]

I have tried your suggestion, but could not matched will you please type the complete formula as i said below =CHOOSE(K3,(I3*L3/100),(I3/L3*­ 100),(I3-I3*L3/100),(I3*L3/100 +I3)) or may you tell me your mail id that i can send to you the file related to the said formula.

[ExcelIsFun]

Try:

=IF(AND(I3="",K3="",L3=""),"", formula)

and where it says formula put your CHOOSE formula

[ExcelIsFun]

I will make a video, but not for at least a week.

=IF(AND(I3="",K3="",L3=""),"", formula)

just says if cell I3 and K3 and L3 are all blank, then show a blank in the cell, otherwise show the formula.

[gajukrish]

yes i followed the same as you have made in the Excel Magic Trick 481: but the problem is if any one cells leaves blank the #value error refelects in M3, if you want know the complete error then please send me your email id and i will send you the excel file to your mail id.

[ExcelIsFun]

Try:

=IF(AND(I3="",K3="",L3=""),"",­formula)

[gajukrish]

continue.. and finally in M3 I got a #value error message whenever the cell values is deleted or leaved blank from any one cell of I3 or L3, the M3 having formual is =CHOOSE(K3,(I3*L3/100),(I3/L3*­100),(I3-I3*L3/100),(I3*L3/100­+I3)) .Thanks.

[ExcelIsFun]

Did you watch the video I made for you? The title is:

Excel Magic Trick 481: Change Operator in Formula from Drop-Down Using CHOOSE function

[gajukrish]

Dear mr.excel, I followed as your guidance but I got the #value error in Cell M3, Here the details are, I3 is 100, J3 is the list of mathematical operation of *,/,-,+ which is converted to 1,2,3,4 as a scroll by option in data validation list mode which is originally selected from F3 to f6. k3 having the range selected from F3,G3,H3 to F6,G6,H6 as follows: =IF(ISNA(VLOOKUP(J3,$F$3:$H$6,­3,FALSE)),0,VLOOKUP(J3,$F$3:$H­$6,3,FALSE)), L3 is 10 continu...

[gajukrish]

DEAR MR. EXCEL, I want to know how can i calculate the two different values in cell by different mathematical operation like subtract, division, multiplication, adds, with the help of scroll option. For example A1 value is 100, C1 value is 10, and i have scroll down and selected the option multiplication * in B1 cell then D1 should be 1000, next if i scroll and select the option + in B1 cell then D1 should be 110 and if i scroll and select the option / in B1cell then D1 shold be 10, Thanks.

[ExcelIsFun]

If 100 is in cell A2 and 10 is in cell C2 and you let 1 represent *,

2 represent /, 3 represent + , and 4 represent - and you put the number 1, or 2, or 3, or 4 in cell B2 you can use the formula

=CHOOSE(B2,A2*C2,A2/C2,A2+C2,A­2-C2)

--excelisfun

[ExcelIsFun]

I'll make a video soon.

[gajukrish]

dear mr.excel i have lot of item with fixed price, each item is having it's fixed price. so may you show me how to setup the list with fixed price without manually typing, for example: if item A price is 100, item B price is 200, item C price is 300, and i scroll and selected the B item in description menu immediately the next cell should be automatically fill by B item value 200 without manually type the price if B, Thanks.

[ExcelIsFun]

You need VLOOKUP. Try this video title:

Highline Excel Class 07: VLOOKUP function formula 7 Examples

[fressy98]

I typed exactly the same thing and even using the same cell : =SMALL(IF(B6:B27=E2;C6:C27;999­);5)

but it keeps giving me and #VALUE!

I also tried with the parenthesis : {=SMALL(IF(B6:B27=E2;C6:C27;99­9);5)}

but same error.

What may I did wrong and what is this parenthesis { } for?

[ExcelIsFun]

# VALUE! error means you did not enter the formula with:

Ctrl + Shift + Enter.

Array formulas require Ctrl + Shift + Enter or Excel gives you a # VALUE! error.

[fressy98]

wow that's amazing! Thanks!

But in which case do we use array formulas? What does that mean? I use array before(for INDEX, LOOKUP) and never use Ctrl+Shift+Enter

[ExcelIsFun]

I have about 100 or more videos about array formulas and 2 playlists. I guess you don't know how to use the excelisfun channel yet. Search for and watch this video title:

excelisfun Search For Excel Videos and Playlists & Download Workbooks

In particular, I have two playlists that explain all you need to know about array formulas. The best play list by far is entitled:

Excel Series: Array Formulas 24 Examples

[fressy98]

Thank you so much for posting all this and for your answers!! :)

[ExcelIsFun]

You are welcome!

[ExcelIsFun]

If you watch this playlist:

Excel Series: Array Formulas 24 Examples

It will answer all your questions like when to use Ctrl + Shift + Enter and when to not and much much more.

Each video is about 20 minutes and there are about 6 videos, and there have a lot of concepts about array formulas!!

[waseemakhlaq123]

hi i am waseem.can u tel me how can i sum of rows or colum which is not includ hidden rows or colum ??i wait ur reply thanks

[ExcelIsFun]

Dear waseemakhlaq123,

If numbers are in A1:A3 and row 2 is hidden, this will add just the non-hidden rows:

=SUBTOTAL(109,A1:A3)

=excelisfun

[waseemakhlaq123]

Dear Sir,

thanks for help me,its great sir

If number are in A1:D1 and colum 2 is hide,how can i add only un-hidde number????

[ExcelIsFun]

Dear waseemakhlaq123,

Try:

=SUM(A1:A3)-SUBTOTAL(109,A1:A3­)

=excelisfun

[waseemakhlaq123]

u send first formula =subtotal(109,A1:A3)

its working but i want to know to u

if number in A1:B1:C1:D1 and we hide B1 and C1 then value should show only unhidde colum otherwise the first formula is working on only rows =subtotal(109,A1:A3) so please send me formula which is working in colum to sum only unhide thanks

[ExcelIsFun]

Dear waseemakhlaq123,

I do not know how to do that.

--excelisfun

[waseemakhlaq123]

ok thanks for ur helps

[waseemakhlaq123]

Dear

how can i convert number into words(vice versa) by using a formula of excel 2007

Regards

waseem

[ExcelIsFun]

Dear waseemakhlaq123,

Do you mean change $125 into the words "one hundred twenty five"? If so there is a great function, but you have to add it in to your Excel. See this video for how to get this add-in:

Excel Magic Trick 276 MoreFunc Excel add-in 66 New functions

--excelisfun

[waseemakhlaq123]

Dear

i get it and install it .i am so happy thanks your are master in excel.May i ask whats ur profession?

[ExcelIsFun]

Dear waseemakhlaq123,

My profession is: Teach As Many People Excel As I Can!

My other job is: teacher at college in Business Department.

--excelisfun

[waseemakhlaq123]

Dear Sir

is there any formula or option in excel 07 that first letter of every word(without pressing shift) become capital for example english into English

[hamy72]

Both tips were informative, although I liked your solution to this Mike! Great Video!

[ExcelIsFun]

Dear hamy72,

I am glad that you liked the video! They sure are fun to make!

--excelisfun

[INGc3z4r]

Those are great tricks thank you both

[ExcelIsFun]

Dear INGc3z4r,

You are welcome! I am glad that you liked them.

--excelisfun