Help with Formula

  • Thread starter Thread starter Jimbo
  • Start date Start date
J

Jimbo

First thank you in advance

My columns look like this

pod Data % Resulting score
1 23 2
1 79 3
1 89 4
2 34 2
2 77 3
2 99 5
3 3 1
3 43 3

My Formula in column 3...

IF(PERCENTRANK($B$2:$B$25,$B2)>=89%,5,IF(PERCENTRANK($B$2:$B$25,$B2)>=74%,4,IF(PERCENTRANK($B$2:$B$25,$B2)>=49%,3,IF(PERCENTRANK($B$2:$B$25,$B2)>=24%,2,IF(PERCENTRANK($B$2:$B$25,$B2)>=-1%,1)))))

What I want is to only compare the Data if they are in the same pod.

How do I do this?
 
First thank you in advance

My columns look like this

pod     Data %  Resulting score
1       23      2
1       79      3
1       89      4
2       34      2
2       77      3
2       99      5
3       3       1
3       43      3

My Formula in column 3...

IF(PERCENTRANK($B$2:$B$25,$B2)>=89%,5,IF(PERCENTRANK($B$2:$B$25,$B2)>=74%,4­,IF(PERCENTRANK($B$2:$B$25,$B2)>=49%,3,IF(PERCENTRANK($B$2:$B$25,$B2)>=24%,­2,IF(PERCENTRANK($B$2:$B$25,$B2)>=-1%,1)))))

What I want is to only compare the Data if they are in the same pod.

How do I do this?
Click to expand...

Um? Right now you are doing it row-by-row.
What do you want to do about the pod column?

Anyway, I think there's probably a select in your future.
Socks
 
On Apr 20 said:
Anyway, I think there's probably a select in your future.
Click to expand...

Oops. Thought I was in the Access news group.

What you will want to do is extract the rows that the pod numbers
change. If they are never ever going to change, you can just
put them in by hand. If not, the easy way is some sort of index
column (just a column with 1, 2, 3, etc., in the cells) and some
simple if statements in the next column to find if the pod
number changes. Then some simple lookup functions, and
you are there. Depending on what you meant to do with the
pod values.

If these extra columns are not acceptable, then you can hide
them by setting their width to zero. Or put them way over to
the right side of the spreadsheet.

If none of that looks acceptable, you could use VB to do it.
A simple little VB routine could calculate the values.
Socks
 
Back
Top