How to sort a group of data that are a result of a formula?
Can anyone help...
I have a table that is made up of 14 answers from formulas elsewhere in the sheet.
It's like this...
- I input a time manually for 14 footballers...e.g. "91.36" (mins/secs) cells - (F2:F15)
- I then input the distance they have ran during that match e.g. "9100" (metres) - (H2:H15)
- Then in cell "I2" i have the equation "=sum(H2/F2)" running in cells (I2:I15). That then gives me the answer i need and all is fine.
- I then have another table which i have set out in order to sort the 14 footballers in order of who has ran the furthest so written in "J2", i have "=I2".
- The problem is that when i come to sort the data, it doesn't move, and i think it's because this data hasn't been input and that it's a result of a formula.
Can anyone help, it would be much appreciated.
Welcome to the Board.
Please post a sample file with dummy data outlining expected results.
In short you will want to set your 2nd sheet to use formula to automatically order the data without having to sort sheet 1 ... this can be achieved relatively easily - what should happen when the calculation in I on sheet1 were to return multiple instances of the same result ? What is the deciding factor in terms of rank in these instances... think of it like Goal Difference being the initial decider if Pts are level, then subsequently Goals Scored etc...
A rough demo is attached based on a generic approach to such a problem, this assumes where mtrs/min result is identical the first differentiating factor is total distance covered (greater = higher rank) - this utilises a helper column (Sheet1!J) - it may be of interest to you ?
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks