Recommend
 
 Thumb up
 Hide
15 Posts

BoardGameGeek» Forums » Everything Else » Chit Chat

Subject: [Solved] Help with excel spreadsheet rss

Your Tags: Add tags
Popular Tags: [View All]
Terry Kirk
United Kingdom
Barnsley
South Yorkshire
flag msg tools
Avatar
mbmbmbmbmb
I have a spreadsheet with a list of 30 numbers.

For each number I want to find:
- How many numbers it is higher than
- How many numbers it is the same as
- How many numbers it is lower than

I'm not sure where to even start. Any help would be highly appreciated.


Solution
kirkatronics wrote:
Hi guys, thanks for all your help, it is really appreciated.

Amongst others, I looked at rank, but it was more complicated than it needed to be.

All the ADVICE jogged my memory on how to use countif correctly.

The formula I used was.
=countif(B2:B31,">"&B2)
B2:B31 is the entire range.
B2 is the value to compare.
> Can be less than, less than our equal to, equal to, equal to our greater than, or greater than.

If I wanted to exclude its self I can just use -1.

I've posted the answer in detail because a Google search didn't bring back anything useful.

 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Billy McBoatface
United States
Lexington
Massachusetts
flag msg tools
KGS is the #1 web site for playing go over the internet. Visit now!
badge
Yes, I really am that awesome.
Avatar
mbmbmbmbmb
Re: Help with excel spreadsheet
Couldn't you just apply a sort to the column, then from glancing at the column position you know the answer?
3 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Matt Robertson
Canada
Regina
Saskatchewan
flag msg tools
Life is Short; Play Games!
badge
The BixCON Series of gaming events: A combination of Great Friends, Great Food, & Great Drink!
Avatar
mb
Re: Help with excel spreadsheet
Have a look at the RANK() function. I think it will give you what you need.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Paul DeStefano
United States
Long Island
New York
flag msg tools
designer
badge
It's a Zendrum. www.zendrum.com
Avatar
mbmbmbmbmb
Re: Help with excel spreadsheet
If you don't know how to start, this can become fairly complex in concept.

Each cell needs to compare itself to every other cell for each of the three cases, with a return of TRUE adding one to a counter cell for each case.

So line 1 compares to line 2. Is it equal? Yes? Then add 1 to the total to the number in cell TOTALEQUALTO1. Then the next case and the next cell until complete.

It's a doable project, but not a novice one.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Matt
United States
Central Coast
California
flag msg tools
0110100110010110
badge
Avatar
mbmbmbmbmb
Re: Help with excel spreadsheet
wmshub wrote:
Couldn't you just apply a sort to the column, then from glancing at the column position you know the answer?


This one.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Barry Harvey
United Kingdom
London
flag msg tools
Avatar
mbmbmbmb
Re: Help with excel spreadsheet
I don't have Excel but there may be a similar function to Openoffice's COUNTIF.

COUNTIF(range; ">" &b2)

will count up the number of cells that in range 'range' that have a value greater than the value in b2.

For the "=" remember to subtract 1 (since I'm assuming that the range includes the cell you're looking at).
4 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Dwayne Hendrickson
United States
Oklahoma City
Oklahoma
flag msg tools
badge
Avatar
mbmbmbmbmb
Re: Help with excel spreadsheet
wmshub wrote:
Couldn't you just apply a sort to the column, then from glancing at the column position you know the answer?


Since some numbers may be equal to others, this approach wouldn't work.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Billy McBoatface
United States
Lexington
Massachusetts
flag msg tools
KGS is the #1 web site for playing go over the internet. Visit now!
badge
Yes, I really am that awesome.
Avatar
mbmbmbmbmb
Re: Help with excel spreadsheet
okiedokie wrote:
wmshub wrote:
Couldn't you just apply a sort to the column, then from glancing at the column position you know the answer?


Since some numbers may be equal to others, this approach wouldn't work.
Not sure why not. If I sort and get:

5
10
10
11
14
14
14
...


It's still clear at a glance, the 14's are greater than 4, equal to two others, and less that however many are below.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Michael Berg
United States
Medford
Massachusetts
flag msg tools
badge
You can count on me!
Avatar
mbmbmbmbmb
Re: Help with excel spreadsheet
caracfergus wrote:
I don't have Excel but there may be a similar function to Openoffice's COUNTIF.

COUNTIF(range; ">" &b2)

will count up the number of cells that in range 'range' that have a value greater than the value in b2.

For the "=" remember to subtract 1 (since I'm assuming that the range includes the cell you're looking at).


This is the easiest answer.

In cells A2 to A31, put your 30 numbers.

Column B will be your counting column.

For cell B2, use the formula =COUNTIF($A$2:$A$31,"<"&$A2). You can then copy this formula and paste it into every cell in column B with a number corresponding in column A.

You can change the < to > or = as needed.
3 
 Thumb up
0.25
 tip
 Hide
  • [+] Dice rolls
Matt
United States
Central Coast
California
flag msg tools
0110100110010110
badge
Avatar
mbmbmbmbmb
Re: Help with excel spreadsheet
okiedokie wrote:
wmshub wrote:
Couldn't you just apply a sort to the column, then from glancing at the column position you know the answer?


Since some numbers may be equal to others, this approach wouldn't work.


There are 30 numbers. Once you sort them, you cand spend 30 seconds finding the blocks of equal numbers and figure out the "placement" positions, with ties.


number place
2 1
4 2
6 3
6 4 3
6 5 3
9 6
13 7
32 8
32 9 8
32 10 8
90 11

If you have 30,000 numbers then the other ideas make more sense.

If this is a homework problem in an Excel/VBA programming class, then okay, do the formula thing. Or write a quick macro. But if this is for work, sort `em.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Erik Dewey
United States
Broken Arrow
Oklahoma
flag msg tools
designer
publisher
badge
Avatar
mbmbmbmbmb
Re: Help with excel spreadsheet
I would use the Rank.EQ function. It tells you where something is ranked in a list, so for numbers 8,6,4,2,10 the 6 would give you a result of 3 (it's the 3 in the list). Subtract the total number of items from the result and you'll know how many items are above it. Subtract 1 from it and it will give you the number of items below it. To see how many items have that value, use the CountIF function.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Isaac Shalev
United States
Stamford
Connecticut
flag msg tools
designer
publisher
badge
Avatar
mbmbmbmbmb
Re: Help with excel spreadsheet
That's a cool function, Erik. I don't think I've ever used it. I would have gone the Countif approach myself.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Terry Kirk
United Kingdom
Barnsley
South Yorkshire
flag msg tools
Avatar
mbmbmbmbmb
Re: Help with excel spreadsheet
Hi guys, thanks for all your help, it is really appreciated.

Amongst others, I looked at rank, but it was more complicated than it needed to be.

All the ADVICE jogged my memory on how to use countif correctly.

The formula I used was.
=countif(B2:B31,">"&B2)
B2:B31 is the entire range.
B2 is the value to compare.
> Can be less than, less than our equal to, equal to, equal to our greater than, or greater than.

If I wanted to exclude its self I can just use -1.

I've posted the answer in detail because a Google search didn't bring back anything useful.
1 
 Thumb up
0.25
 tip
 Hide
  • [+] Dice rolls
Agent J
United States
Coldwater
Michigan
flag msg tools
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metal-themed attributes. His fur is water tight and he's always up for a fight.
badge
He's a semi-aquatic egg-laying mammal of action. He's a furry little flat-foot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.
Avatar
mbmbmbmbmb
I've used Rank for one thing ever.
I made a rankings table for a tournament we were having. Have all the results on a hidden worksheet, and then use 'rank' to figure out where it should go on the front page, so that updating results did not require anything to be done to the front page - it would reorder itself into the current rank, and then a vlookup function or an index/match function would bring over the rest of the data.

Countif was definitely the right formula for this one.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Terry Kirk
United Kingdom
Barnsley
South Yorkshire
flag msg tools
Avatar
mbmbmbmbmb
Jythier wrote:
I've used Rank for one thing ever.
I made a rankings table for a tournament we were having. Have all the results on a hidden worksheet, and then use 'rank' to figure out where it should go on the front page, so that updating results did not require anything to be done to the front page - it would reorder itself into the current rank, and then a vlookup function or an index/match function would bring over the rest of the data.

Countif was definitely the right formula for this one.


When I started it just became so obvious.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Front Page | Welcome | Contact | Privacy Policy | Terms of Service | Advertise | Support BGG | Feeds RSS
Geekdo, BoardGameGeek, the Geekdo logo, and the BoardGameGeek logo are trademarks of BoardGameGeek, LLC.