Can I set a minimum/maximum range for a certain basic formula?

[Robmeister89]

So right now I'm doing a power poll for 32 teams with power polls from other people and its very simple..

=((AVERAGE(A29:V29))*5)/32

However, the lowest possible rating would be 0.156... I would like to make it to where 0.000 is the lowest possible rating.

Is it possible to do this?

[gak67]

The only way to do this is if the average of A29:V29 was 0.

[Robmeister89]

The only way to do this is if the average of A29:V29 was 0.

That's not true.. I remember in the past I had it set to where 0 was the lowest but I forgot how and I no longer speak to the person who helped me all that long time ago..

[gak67]

Logically, as soon as the average of the values in A29:V29 is anything above 0 it will get multiplied by 0.15625 (5/32), so with the formula you have, assuming the values in in A29:V29 are positive, the only way to get a 0 return is to have an average of 0 in A29:V29.

Can you clarify what you are asking? Are you wanting to show the 0.15625 as 0, ie. round it down to 0? If so how many decimal places do you want to go to? Or is it something else you are trying to do?

[Robmeister89]

I'm not trying to round down because most of the ratings will be below 1 and if that were the case we'd have a lot of 0's.. I'm not trying to change 1.56 to 0 I can do that easily.. I want to adjust the range of the formula to be 0-5 (if the team receives all 32's they will be ranked as a 5.00, if they receive all 1s they will be ranked at 0.00).

I know its possible as its been done before I just cannot for the life of me figure out how it was done the first time.

[gak67]

I think the solution is to simply subtract 1 from the average, and the divisor. Others will correct me if I'm wrong.

Please Login or Register  to view this content.

[Robmeister89]

That seems to do the trick. Thanks.