re: Excel Formula Question

Just represent them as fractions with your lowest as the numerator and the data point as the denominator

PERCENTILE(array,k)

Just stopped in to say I really hate Excel. Trying to figure out formulas like that are just so gd un user friendly.

quote:

---

I need the lowest number represented as 1

---

That part is easy. Let’s say the range is A1:A10 and you are starting with A1.

IF(A1=MIN($A$1:$A$10),1,[insert value/formula for numbers that are not the smallest])

quote:

---

and then the greater numbers show as less than one

---

This part isn’t clear from your example. Are you trying to scale them from 0-1? Trying to do some sort of fractional difference? Or what?

Worst case, you define your formula for the remaining numbers and insert it into the last part of the IF function above.

Depending on what your actual goal is there may be a better way than using IF functions.

This looks like a homework assignment from a professor that has no clue what we use Excel for in industry.

Either make this data set 8000 rows or go home.

Can you not just sort the cells greatest to least and in the next column enter 1, .99, .98, auto fill the rest?

Mikelbr will do your homework for you if you let him give you a reach around.

Sort with lowest number at top and round it to 1. The do an if/then if any of the other numbers are not = to the lowest number then make them negative by multiplying by -1

Divide the minimum in the range by each number. Will result in min/min = 1. All others will be less than one.

If range is a1:a25, then in cell b1 enter this:

=min($a$1:$a$25)/a1. Then copy the formula all the way to b25.