Referencing numbers

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.

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

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 my formula......

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

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

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


Any workarounds ?

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.
--
Kevin Backmann


"koit" wrote:

>
> 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.
>
>
> --
> koit
> ------------------------------------------------------------------------
> koit's Profile: http://www.excelforum.com/member.php...o&userid=31100
> View this thread: http://www.excelforum.com/showthread...hreadid=507665
>
>

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" wrote:

>
> 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.
>
>
> --
> koit
> ------------------------------------------------------------------------
> koit's Profile: http://www.excelforum.com/member.php...o&userid=31100
> View this thread: http://www.excelforum.com/showthread...hreadid=507665
>
>

Thanks ever so much.

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

Much obliged.

Keep well