Hello,
I am having trouble figuring out a formula that will locate the top 5 smallest percentages closest to zero from Column J. I want these 5 smallest numbers to go to column D31:D35 I almost have it i think; this is the formula I am using:
=INDEX($J$4:$J$20,MATCH(SMALL(INDEX(ABS($J$4:$J$20),0,1),1),INDEX(ABS($J$4:$J$20),0,1),0))
This formula is not picking up the -0.01%; I am getting a "NUM!" error in Column C33:C34
Can anyone help me out with this please????
Thank You
Missing an ABS
In C31 (Arrayed)
=INDEX($A$4:$A$20,SMALL(IF(ABS($J$4:$J$20) =ABS(D31),ROW($J$4:$J$20)-ROW($J$4)+1),COUNTIF(D$31:D31,D31)))
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
I tried using your formula and I am still getting a NUM! error. Again, I am a novice with excel but was wondering if we fixed D31:D35 to report the Negative numbers as opposed to positive numbers, it may fix the NUM! error. Cells D33 and D34 should say -0.01% not 0.01%. I think it has something to do with the ABS in my formulas in for the range D31:D35.
Sorry for the late response. I was on a business trip. When you get a chance, can you let me know your thoughts?
Thanks for your time with this. I will definitely add to your reputation if you can help me with this.
This is marked solved but your post says you are still having problems. Here is your spreadsheet with the formula changed as I suggested. No #NUM errors.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks