percentage problem.

[FreakyGirl]

hello all

i collect 3 months worth of scores broken down into how many times a player has gotton a certain score(X:AF). the scores can vary from 0-8 only.

if a player has never got a 7 for example, under the 7 column it will show as 0 as expected.

those scores are then converted into % for the chance they may get that score the next time they play(A:I). this also works as expected.

but a player always has a chance at any score even if they never have achieved it before. therefore i would like anywhere there is a 0% to say 1%.

i tried the following but the results arent right.

IF(A4=0%,1%,A4) but i couldnt get it to work.

so i made another score table (N:V) & used a IF to change any 0 to 1. that really distorted the %. the 0% turned into 6% & reduced the other % for the adjustment.

so i adjusted the IF to change any 0 to a 0.1 rather than just a 1. this worked for the 1st row perfectly & didnt even change any of the other %. so i thoguht i was onto something. but.. (theres always a but) when double clicking to send the formula down the column it worked for some & not others.

i thought perhaps the num of 0's (columnW) made a diff. but there are others with the same number of 0's as the row that works so that didnt make sence.

i think im on the right track but not sure where to turn from here. any help would be appreciated.

thanks,
FreakyGirl

 

[Hui]

Hi Freaky

What I did was change the formula in the first 8 columns to calculate the percentage but subtracting the 0.01 from each of the Highest score
In the first Row there are two cells with 0.01 and one cell with 5
So the 0.01 was subtracted twice from 5 so it is now 4.98
and the percentages are then calculated based on these new values

If there are 2 maximum values and say 3 values of 0.01 each maximum value will have (3/2) x 0.01 subtracted from it to even things out etc


I also added a named formula called SmallAmt and it is 0.01
you can change that and it all flows through

 

[FreakyGirl]

Hello again.

THANKS for the fix & the tip

naming the formula allowed me to play with the numbers much easier then doing that change column by column. what a timesaver!

i was still getting 0% in the columns when i copied to my worksheet but after changing the fomatting to % with 2 decimal places it worked properly.

have a great day!
FreakyGirl