[corrin]

I would appreciate some help with an excel spreadsheet im trying to finish, I need to create a formula.
What my goal for the formula to do is:
Sort teams by a score into three tiers.
each team has a C.Score, each score is between 1 and -2. the score determines what tier the team is in, with the frist tier being teams with score above 0, and the second tier being teams with scores between -1 and 0, and third tier being teams with scores between -1 and -2. once the teams are seperated into tiers, they need to be ranked, highest score to lowest within that tier.

how my spreadsheet is set up, is the league standings on sheet 1,
the team and current score(calculated from standings on sheet1) on sheet 2, and then the three tiers on sheet 3, to be determined by the scores on sheet 2.

I have successfully gotten the scores to calculate across sheets, but i can not find a formula that would suit my need for the teams to be ranked into their teirs.

I would soo appreciate any help on this, im anxious to finish this project, i will be testing the sytem for this season and playoff season to determine its effectiveness. If it is proven effective, i will be presenting it to the league to replace the current point system! They are looking forward to seeing my point system work.

The spreadsheets are attached. file name: NHL standings

Thanks for your interest,
Corrin

[Advertisements]

Corrin,
It appears that your spreadsheet failed to attach to your post.
You can use a nested if statement to determine the tier.

0.A.........|.....B.......|.....C.....|.....D.....|
1.Team...|.C.Score.|.Tier…..|
2.Bulls....|..-2.........|
3.Braves.|.0...........|
4.Cats....|.1............|
5.Dogs....|.-1..........|

in cell C2 try the following formula
=IF(A2<-1,"T3",IF(A2>0,"T1","T2"))

hope this helps

dsm

[dsm]

Corrin,
It appears that your spreadsheet failed to attach to your post.
You can use a nested if statement to determine the tier.

0.A.........|.....B.......|.....C.....|.....D.....|
1.Team...|.C.Score.|.Tier…..|
2.Bulls....|..-2.........|
3.Braves.|.0...........|
4.Cats....|.1............|
5.Dogs....|.-1..........|

in cell C2 try the following formula
=IF(A2<-1,"T3",IF(A2>0,"T1","T2"))

hope this helps

dsm

[corrin]

Okay! i got that part to work! thanks!
I added a column to each conference to fit a tier column value for each team, and entered that formula.

The next step is to get a formula to put the tier values for each team, into its tier column by entering the teams name, with the teams in their tier, in desending order. so that, all 1st, 2nd, and 3rd tier teams can be viewed in their respective tiers and in decending order.

I was thinking maybe a combination of IF and RANK functions might work, but i really have no idea.
I tried to upload the file, but the website wouldnt let me saying "You are not permitted to upload a file with that file extension." so if we need, i can e-mail the file to you.

i really appreciate your help!
-Corrin

[piper]

I may be missing something... but once you have the tiers in the cell, based on the previous formula, why don't you do a sort by Tier then by Team Name.

[dsenette]

I tried to upload the file, but the website wouldnt let me saying "You are not permitted to upload a file with that file extension." so if we need, i can e-mail the file to you.

you would need to zip the file before attaching it...as we don't allow .xls files