software

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 percentageI 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: www.excelforum.com/member.php...oamp;userid=31100
View this thread: www.excelforum.com/showthread...hreadid=507665
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--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=507665
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 100amp; 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 ?--
koit
------------------------------------------------------------------------
koit's Profile: www.excelforum.com/member.php...oamp;userid=31100
View this thread: www.excelforum.com/showthread...hreadid=507665You 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 Backmannquot;koitquot; wrote:

gt;
gt; I have a s/sheet set up where I want to average percentages.
gt;
gt; I have 3 columns
gt;
gt; 1 where actual percentages are entered
gt; 2 where a number (1-5) is entered - no other numbers should be allowed
gt; 3 the total column that works out the overall percentage
gt;
gt;
gt; I can do parts 1 and 3 not a problem.
gt;
gt; I'm struggling with part 2 in terms of getting those numbers, 1-5 to be
gt; converted to percentages that I want.
gt;
gt; I need :-
gt;
gt; 1 to equal 0%
gt; 2 = 25%
gt; 3 = 50%
gt; 4 = 75%
gt; 5 = 100%
gt;
gt; How do I do that and additionally, how do I ensure that the user can
gt; ONLY input 1-5 in that particular column.
gt;
gt; Once I can get the percentages for column 2, I can then average that
gt; and column 1 into column 3.
gt;
gt; Any help would be most welcome.
gt;
gt; Thanks.
gt;
gt;
gt; --
gt; koit
gt; ------------------------------------------------------------------------
gt; koit's Profile: www.excelforum.com/member.php...oamp;userid=31100
gt; View this thread: www.excelforum.com/showthread...hreadid=507665
gt;
gt;

To allow only whole numbers 1-5:
1. Data-gt;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

quot;koitquot; wrote:

gt;
gt; I have a s/sheet set up where I want to average percentages.
gt;
gt; I have 3 columns
gt;
gt; 1 where actual percentages are entered
gt; 2 where a number (1-5) is entered - no other numbers should be allowed
gt; 3 the total column that works out the overall percentage
gt;
gt;
gt; I can do parts 1 and 3 not a problem.
gt;
gt; I'm struggling with part 2 in terms of getting those numbers, 1-5 to be
gt; converted to percentages that I want.
gt;
gt; I need :-
gt;
gt; 1 to equal 0%
gt; 2 = 25%
gt; 3 = 50%
gt; 4 = 75%
gt; 5 = 100%
gt;
gt; How do I do that and additionally, how do I ensure that the user can
gt; ONLY input 1-5 in that particular column.
gt;
gt; Once I can get the percentages for column 2, I can then average that
gt; and column 1 into column 3.
gt;
gt; Any help would be most welcome.
gt;
gt; Thanks.
gt;
gt;
gt; --
gt; koit
gt; ------------------------------------------------------------------------
gt; koit's Profile: www.excelforum.com/member.php...oamp;userid=31100
gt; View this thread: www.excelforum.com/showthread...hreadid=507665
gt;
gt;


Thanks ever so much.

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

Much obliged.

Keep well--
koit
------------------------------------------------------------------------
koit's Profile: www.excelforum.com/member.php...oamp;userid=31100
View this thread: www.excelforum.com/showthread...hreadid=507665