# Excel formulas Help

Occasional Contributor

# Excel formulas Help

Hi,

This is probably simple, but.......

i need a formula to give me a score of a field of results.

x axis score of 9 or 10 = 100%     8 to 5 =50%     4 to 0 = 0%      Total is percentage of results

this needs to be in approx 30 cells on x axis

So every time i type in a score 0 - 10 it gives a result as a percentages.

Am I over thinking this, i just cant work it out!

Thanks for any help

Dino

11 Replies

# Re: Excel formulas Help

Let's say your results are in B2:B30.

Enter the following formula in C2:

``=IF(B2<=4,0%,IF(B2<=8,50%,100%))``

Fill down to C30.

# Re: Excel formulas Help

As variant

``=INT((A1-0.5)/4)/2``

# Re: Excel formulas Help

Hi, thanks for the reply, but it has not worked.
I just get a value of 1 in the cell
I need to go from C5 to L5
If i try to extend the firmula by:
=IF(C5,L5<4,0%,IFC5,L5<8,50%,100%))
That does not work either
Any help would be gratefully appreciated.
Thanks

# Re: Excel formulas Help

Hi this works for individual cells, but i need it to correlate and sum C5 to L5, then C6 to L6, and so on
Any more help is appreciated
Thanks

# Re: Excel formulas Help

Perhaps

``=IF(SUM(C5:L5)<4,0%,IF(SUM(C5:L5)<8,50%,100%))``

This can be filled down.

If that does not do what you want, please explain more clearly what you want to accomplish.

# Re: Excel formulas Help

Hi,
No, this is not right either.
It just returns a value of 1.
I need the end value to be in percentage form.
So, if the scores are 10, 10, 10, ect, the final column would show 100%
But if the scores are 8, 8, 8 this would show 50%
But I also need the formula to work out the percentages, if the scores were 10, 8, 8
I hope this is clearer.
Thanks
Darren

# Re: Excel formulas Help

Please provide a series of examples that comprehensively show what you want.

# Re: Excel formulas Help

So, in the attached pic:
C3,D3,E3 would = 100%
F3 would = 50%
I need a total in percentage in G3
In row 14
C14 = 50% D14 = 0% E14 = 0% but if I changed F14 to a score of 9, that would = 100%
So the total in G14 is a running percentage.
I have attached a condensed pic, the actual chart will be bigger
I hope this makes more sense
Thanks
Darren

# Re: Excel formulas Help

That's not clear at all, since you don't tell us what the expected outcome should be.

Perhaps in G3:

``=SUMPRODUCT(LOOKUP(C3:F3,{0,5,9},{0,0.5,1}))``

Format G3 as a percentage and fill down.

# Re: Excel formulas Help

Hi,
The expected outcome is a percentage of the scores in in C3- F3
So: C3 = score 9 which is 100%
D3 = score 10 which is 100%
E3 = score 9 which is 100%
F3 = score 8 which is 50%
So G3 (TOTAL)needs to be an overall percentage of C3,D3,E3 and F3
a score of 9, 10 = 100%
a score of 8,7,6,5,4 = 50%
a score of 3,2,1,0 = 0%
I hope this is clearer
Thanks
Dino

# Re: Excel formulas Help

@Dinodog I think what you want is to take and average of the % values

``=AVERAGE(INT(C3:F3/4.1)/2)``

and fill down.

unless you want the original numbers (9, 10, 9 ,8) to be averaged first and then have the % transformation applied, but I don't think so.