Excel Code

Potential-Monke

Hey all,

I've gotten myself involved in an "Operation Transformation" type thingy, and i've been asked to create an Excel sheet for progress. I've it all on one workbook with multiple sheets. It's not on this computer, so i can't upload a screenshot of it, this is the basic premise:

- Sheet 1: 7 blocks with team name, members, individual and total weight
- Sheet 2-8: Team weekly loss
- Sheet 9: Totals
- There are 7 teams, and each week i have it set up that i enter the weight lost and it calculates the % of total lost. This is entered beside each individual in sheets 2-8.
- Each team is shown on the final sheet with their % loss and lbs lost, per team, per week. This auto populates through calculations and references to cells in sheets 1-8

What i need is, that on sheet 9 (total), there will be another grid which will list the highest Team % loss in order of biggest % to smallest %. What i want to do is, instead of the % showing up (which is simple MAX(xxxxx)), i want the team name associated with that percentage to show up.

IE:
|Week 1|Week 2
Team 1|4.0%|3.9%
Team 2|3.5%|4.1%
Team 3|4.2%|2.7%
Team 4|3.9%|3.1%


|Week 1|Week 2
Leader|Team 3|A
Second|Team 1|B
Third|Team 4|C
Fourth|Team 2|D


I want A to equal Team 2, B to equal Team 1, etc. (Also, i can do the MAX formula to get the top team, but how do i do second, third, fourth, etc?)

Don't know if i'm explaining it well, and i should have a screenshot at some stage tomorrow, will post it up then. It's been way too long since i done anything like this in excel!

homer911

VLOOKUP?

Potential-Monke

Won't do the job unfortunately, VLOOKUP requires the information which decides the order to be in the first column, whereas the first column contains the team names!

Head melted, think i might just tell them to manually enter it every week!

mister_minn

Potential-Monke wrote: »

VLOOKUP requires the information which decides the order to be in the first column, whereas the first column contains the team names!

So why not copy the first column to the end, either directly or with formulae (=a1 etc.). You can hide these cells to keep it looking right

Then, do the lookups as per http://stackoverflow.com/questions/4300450/finding-cell-in-another-column-associated-with-max-value-in-excel using the relevant 2nd, 3rd, 4th etc. column as the start range for the VLOOKUP

gctest50

Potential-Monke wrote: »

..........
What i need is, that on sheet 9 (total), there will be another grid which will list the highest Team % loss in order of biggest % to smallest %. What i want to do is, instead of the % showing up (which is simple MAX(xxxxx)), i want the team name associated with that percentage to show up.
.......

You could show a pie-chart too ?


http://office.microsoft.com/en-001/excel-help/present-your-data-in-a-pie-chart-HA010211848.aspx

jabberwock

I think this is what you want.

worked on from this post -horribly long microsoft forum link

namenotavailablE

A combination of INDEX and MATCH functions should do it I think. It would be easiest if you could attach the workbook.

jabberwock

this is in the xls above. The formula is drag-able to autofill the rest.