Terry Kirk
United Kingdom Barnsley South Yorkshire

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.

Billy McBoatface
United States Lexington Massachusetts
KGS is the #1 web site for playing go over the internet. Visit now!
Yes, I really am that awesome.

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?

Matt Robertson
Canada Regina Saskatchewan
Life is Short; Play Games!
The BixCON Series of gaming events: A combination of Great Friends, Great Food, & Great Drink!

Re: Help with excel spreadsheet
Have a look at the RANK() function. I think it will give you what you need.

Paul DeStefano
United States Long Island New York
It's a Zendrum. www.zendrum.com

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.

Matt
United States Central Coast California
0110100110010110

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.

Barry Harvey
United Kingdom London

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).

Dwayne Hendrickson
United States Oklahoma City Oklahoma

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.

Billy McBoatface
United States Lexington Massachusetts
KGS is the #1 web site for playing go over the internet. Visit now!
Yes, I really am that awesome.

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.

Michael Berg
United States Medford Massachusetts
You can count on me!

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.

Matt
United States Central Coast California
0110100110010110

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.

Erik Dewey
United States Broken Arrow Oklahoma

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.

Isaac Shalev
United States Stamford Connecticut

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.

Terry Kirk
United Kingdom Barnsley South Yorkshire

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.

Agent J
United States Coldwater Michigan
He's looking real sharp in his 1940's fedora. He's got nerves of steel, an iron will, and several other metalthemed attributes. His fur is water tight and he's always up for a fight.
He's a semiaquatic egglaying mammal of action. He's a furry little flatfoot who'll never flinch from a fray. He's got more than just mad skills, he's got a beaver tail and a bill.

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.

Terry Kirk
United Kingdom Barnsley South Yorkshire

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.


