Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

I need help making an Excel formula


  • Please log in to reply

#1
corrin

corrin

    New Member

  • Member
  • Pip
  • 2 posts
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
  • 0

Advertisements


#2
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
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
  • 0

#3
corrin

corrin

    New Member

  • Topic Starter
  • Member
  • Pip
  • 2 posts
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
  • 0

#4
piper

piper

    Retired Staff

  • Retired Staff
  • 2,459 posts
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.
  • 0

#5
dsenette

dsenette

    Je suis Napoléon!

  • Administrator
  • 26,019 posts
  • MVP

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
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP