Forum Discussion
Solved
How to display multiple items with the same value ranking
Hi, I've got a report that pulls in data from a cube source and I then sort it using a macro to rank all machines by percentage of availability for the time period. However some users of the report are not comfortable with using the macro and to be honest it's a bit of a rough solution. Other parts of the report use an Index/Match function to automatically rank other data and I have attempted to replicate that but there are times when multiple machines report the same value - most commonly when they are 100% available or 0% available. As a result I end up with the first machine which reports that value repeating in the place of the other machines with that value:
I've tried a number of solutions I've found online but nothing that will display all machines with their accurate titles.
I'm running Windows 10 Enterprise, with
Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20704) 64-bit.
Edit: File located here- https://docs.google.com/file/d/1rOHkJAkiJInHGcMANIAqp_2brtqLdDZw/edit?usp=docslist_api&filetype=msexcel
Any assistance will be appreciated.
Thanks, Kevin
It is claimed that Excel 365 is Turing complete; that is any calculation that might be performed in any other computer language (eg VBA macros) can be performed using Excel's formula language.
Here, I called the data table taken from cube 'Source'.
In which case the output table is given by
= SORT(Source, 2)The greatest obstacle to using 365 effectively can be the user's existing knowledge of spreadsheet techniques?
It is claimed that Excel 365 is Turing complete; that is any calculation that might be performed in any other computer language (eg VBA macros) can be performed using Excel's formula language.
Here, I called the data table taken from cube 'Source'.
In which case the output table is given by
= SORT(Source, 2)The greatest obstacle to using 365 effectively can be the user's existing knowledge of spreadsheet techniques?
"any calculation that might be performed in any other computer language (eg VBA macros) can be performed using Excel's formula language"
Are you sure? What about cell properties and staff like time stamp?
The main things I used VBA for were event handlers and adjusting the properties of shapes so that diagrams adjust to conform to underlying data sets. I certainly can't do that with an Excel formula but then, I am not sure it falls within the scope of Alan Turing's envisaged computational machine either.
PeterBartholomew1 Perfect - this gave exactly the results I needed. Thanks so much for your help!
Here is an example in the inserted file.
Maybe it will help you further with your plans.
You just have to see if these formulas also work in Google Sheets.
As far as Google Sheets is concerned, my knowledge is limited.
- Thanks for that, I'm working in Excel but had to load it onto google drive so I could share it. I'll give that a go and see if it works for me
