Convert a series of numbers to a 1 to 5 scale relative to the highest and lowest

[mazssj]

I have a series of numbers ranging from 75 to 90 (as an example) and I want each of these numbers to converted or related to a scale of 1 to 5.
75 because it's the lowest value it should be an 1, 90 because it's highest value should be 5.

Thanks

 

[Ron Coderre]

@Markmzz
I'm curious about the modification. It's not any simpler and seems to return the same values.
Did you find an error in the formula I posted?

 

[markmzz]

@Markmzz
I'm curious about the modification. It's not any simpler and seems to return the same values.
Did you find an error in the formula I posted?

Hi Ron,

No, your formula is Ok.

The modification only use 6 functions calls and not 7 functions calls.

Markmzz

 

[Ron Coderre]

Ah..I see where you were going with that.
Function count (or formula length) shouldn't be the only measure of formula efficiency.
I have only 3 functions that act on an entire column and the formula you posted has 4. I'd expect the formula I posted to be more efficient.

 

[markmzz]

Maybe you're right.

The modification formula is only another way (with a small modification).

Here is another way:

=IF(A1="","",CEILING((A1-MIN(A:A)+1)/((MAX(A:A)-MIN(A:A)+1)/5),1))

Markmzz

 

[markmzz]

Another way:

=IF(A1="","",MIN(INT((A1-MIN(A:A))/((MAX(A:A)-MIN(A:A))/5))+1,5))

Markmzz

 

[markmzz]

A small modification:

=IF(A1="","",CEILING(MAX(A1-MIN(A:A),1)/((MAX(A:A)-MIN(A:A))/5),1))

Markmzz

 

[markmzz]

A small modification:

=IF(A1="","",CEILING(MAX(A1-MIN(A:A),1)/((MAX(A:A)-MIN(A:A))/5),1))

Markmzz

The formula above isn't ok, so try this:

=IF(A1="","",CEILING((A1-MIN(A:A)+1)/((MAX(A:A)-MIN(A:A)+2)/5),1))

Markmzz

 

[gregumm]

I have a similar issue, but I would like the results to come back between 1 and 5, but include at least 1 decimal point. When I attempted to do this by simply min maxing at 10 and 50, the results all came back in intervals of 10.

=IF(I1="","",MIN(MAX(CEILING((I2-MIN(I:I))/((MAX(I:I)-MIN(I:I))/50),10),10),50))

Any thoughts?