Referencing numbers

[koit]

I have a s/sheet set up where I want to average percentages.

I have 3 columns

1 where actual percentages are entered
2 where a number (1-5) is entered - no other numbers should be allowed
3 the total column that works out the overall percentage


I can do parts 1 and 3 not a problem.

I'm struggling with part 2 in terms of getting those numbers, 1-5 to be
converted to percentages that I want.

I need :-

1 to equal 0%
2 = 25%
3 = 50%
4 = 75%
5 = 100%

How do I do that and additionally, how do I ensure that the user can
ONLY input 1-5 in that particular column.

Once I can get the percentages for column 2, I can then average that
and column 1 into column 3.

Any help would be most welcome.

Thanks.

 

[Dav]

Im not entirely clear but to change 1 to 5 to your percentages

(number-1)/4 should surfice

eg (a1-1)/4 if the number is in cell a1

if you select the cells go to data, validation and choose allow whole
number between 1 and 5 that should do the validation

Regards

Dav

 

[koit]

Thanks mate

That seems to have done the trick. Cheers

One thing that gets me though is this..........

sometimes in column 3 there will not be a value..........and so m
formula......

=AVERAGE((E18:G18), ((I18-1)/4))

Where E18 is column1, G18 is column 2 and the other bit is th
converting the whole number to a percentage..........

in one instance I have 100& in col1, 0% in col2 but I dont have a valu
in column 3. The true average should be 50%, yet excel is taking colum
3 as 0% and then averaging to 25%.


Any workarounds

 

[Guest]

You can use data validation to limit the value in the column to 1 through 5
and the following formula will return your percentage based upon a number 1
through 5, where the number is in Cell A1.
1 2 3 4 5
=CHOOSE(A1,0,0.25,0.5,0.75,1)

Click DATA in the menu and select DATA VALIDATION. In the SETTINGS tab,
select WHOLE NUMBER and set the minimum value to 1 and the maximum value to
5. Optionally, you can add an error alert message if the user enters a value
less than 1 or greater than 5 by clicking on the ERROR ALERT tab and adding
message text.

 

[Guest]

To allow only whole numbers 1-5:
1. Data->Validation
2. Select Whole Number
3. Select Between
4. insert 1 in Minimum
5. insert 5 in Maximum

you can't actually convert the number to a percentage automatically (without
using code that is), but you can have column 3 treat it as a percentage. You
might be able to use something like this formula.

=(A1+(A2-1)*0.25)/2

 

[koit]

Thanks ever so much.

That worked a treat, if not being a really long winded formula.

Much obliged.

Keep wel