How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6122
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

I am using Excel within Office 365 and using a macro to give

Customer Question

I am using Excel within Office 365 and using a macro to give me the results i need,
Within one column of numbers how can I see the 6 highest or 6 lowest numbers in that column. What would the formula be??
Submitted: 2 months ago.
Category: Microsoft Office
Expert:  Jess M. replied 2 months ago.

Hi, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.

Can you send me a sample excel file with dummy data? You can use the Add Files button to attach it here.

Also, are you trying to show the 6 highest or lowest value in an adjacent column?
Please let me know by replying to me here so that I can help you further.

Best regards,
Jess

Customer: replied 2 months ago.
Thanks Jess,
No not in an adjacent column, I hoped the column that all of the figures are in could show only the highest or lowest 6
Expert:  Jess M. replied 2 months ago.

Hi thank you for writing back. Are you saying that instead of shwlowing that entire column, you want to only show the 6 highest or lowest number?

Customer: replied 2 months ago.
Expert:  Jess M. replied 2 months ago.

Can you send me a copy of your file so that I can check it myself? You can "save as" the file to make a copy, remove sensitive info and attack h it when you reply.

Customer: replied 2 months ago.
Thanks, ***** ***** AE -- I need to see the 6 highest figures, can I do this in the same column? and in other circumstances the same will apply to column BC.
Expert:  Jess M. replied 2 months ago.

Thank you for the sample file. Give me a moment to download the file and check your requirements. I will be back shortly.

Expert:  Jess M. replied 2 months ago.

I have checked your file. Column AE is a result of a formula therefore you cannot only show the 6 highest or lowest of the figures. However, you can use another column, enter a formula in that other column utilizing the values in AE, and will only display he 6 highest or lowest figures.

Another option if possible is to color code or highlight the 6 highest or lowest values in the column so that there will be no need to use an extra column.

Customer: replied 2 months ago.
Thanks Jess,
Using another is ok, what would the formula be if I wanted that info in say column BR?
How can i colour code the numbers in the AE column?
Thanks
Expert:  Jess M. replied 2 months ago.

Unfortunately, I see that the column is FILTERED. That is, several rows are hidden due to the filter. Formulas and color coding does not exempt those hidden values. That is, in the sample file you sent me, there are only 29 visible rows. When doing the formula and the color coding, the hidden rows are included. Thus, the color coded cells may be hidden cells and you might think that the formula or color coding is not working.

Expert:  Jess M. replied 2 months ago.

Is column AE really a filtered column? Or is the expected ranking formula allowed to use the entire column data for ranking and displaying the highest or lowest 6? Please let me know so that I can build the formula for you.

Customer: replied 2 months ago.
Wait I will send the the whole file.
Expert:  Jess M. replied 2 months ago.

Ok. But my concern is, will the entire column be ranked for the 6 highest or lowest? If yes, then formulas and the color coding technique will surely work. But if the column is filtered, the data in the entire column will still be used and the formula method utilizing another column will work, since it will just pullout the highest or lowest 6 values. But in the the color coding approach, the color coded cells can be hidden when they are included in the filter.

Customer: replied 2 months ago.
At this point the column is not filtered.
Expert:  Jess M. replied 2 months ago.

Ok, and you want to work on column AE?

Customer: replied 2 months ago.
Expert:  Jess M. replied 2 months ago.

Ok, give me a moment. I will work on AE and on BR as you indicated showing both color coding and formula options.

By the way, using the color coding, we will utilize Conditional Formatting.

  1. Select the entire column AE
  2. In the HOME tab, under Styles, select Conditional Formatting
  3. Point to Top/Bottom Rules
  4. Select Top 10 Items
  5. It shows 10 there in the dialog box, change it to 6 since you want to only show 6
  6. Then select the formatting you want to apply in the results
  7. Click OK
  8. Do steps 1 to 3, then select Bottom 10 Items
  9. Change 10 to 6 and then select the formatting you want
  10. Click OK
  11. The top 6 and the bottom 6 items will then be highlighted. Duplicates are treated as one in rank so it can show more than 6 actually.

I will now work on the formula option and will send you the file later.

Thank you,

Jess

Customer: replied 2 months ago.
Thanks Jess I will Try now. Thanks
Expert:  Jess M. replied 2 months ago.

Thank you for patiently waiting. Due to duplicates, like the color coding results, the ranking is dominated by duplicates. Also, blank cells are treated as a 0 thus the lowest 6 are all zeros.

Here is the final file showing the formula and helper columns I used:

http://filesxpress.com/d-eb5c2585

Customer: replied 2 months ago.
im afraid that does not work at my end
Expert:  Jess M. replied 2 months ago.

why? were you able to download the file I sent you? the formula is working there

Customer: replied 2 months ago.
Where is the result of Of column AE -- I have #VALUE in BN, BO and BQ
Expert:  Jess M. replied 2 months ago.

Oh, a bit strange. My work was not saved. Give me a moment

By the way, I placed the info at the bottom of the column AE

Expert:  Jess M. replied 2 months ago.

Oh sorry, my work was saved and it is at the bottom of the row AE. Please look at cell AE3002 of the file I gave you

Customer: replied 2 months ago.
I see it. Can that be part of the formula that is already in AE, Im afraid i have an appointment for the next 2 hours I will try again then
Expert:  Jess M. replied 2 months ago.

Unfortunately, that will not work. Column AE is a result of a formula and we cannot enclose these formula to make the ranking. Also, the ranking formula needs to test the entire column AE to check the ranking, thus, it should be stored in another cells or column.

The formula I created for you is a dynamic ranking, you can change it to 10, 15, or any number and the results will automatically change. But I only set it to 6 based on your request.

Here, I expanded the table to fit 15 rows. Unfortunately, they still show duplicate values since AE has a lot of duplicate data (See COUNT results in AC3005 and AC3021.