Excel formulas Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1872642%22%20slang%3D%22en-US%22%3EExcel%20formulas%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1872642%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EThis%20is%20probably%20simple%2C%20but.......%3C%2FP%3E%3CP%3Ei%20need%20a%20formula%20to%20give%20me%20a%20score%20of%20a%20field%20of%20results.%3C%2FP%3E%3CP%3Ex%20axis%20score%20of%209%20or%2010%20%3D%20100%25%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B8%20to%205%20%3D50%25%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B4%20to%200%20%3D%200%25%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Total%20is%20percentage%20of%20results%3C%2FP%3E%3CP%3Ethis%20needs%20to%20be%20in%20approx%2030%20cells%20on%20x%20axis%3C%2FP%3E%3CP%3ESo%20every%20time%20i%20type%20in%20a%20score%200%20-%2010%20it%20gives%20a%20result%20as%20a%20percentages.%3C%2FP%3E%3CP%3EAm%20I%20over%20thinking%20this%2C%20i%20just%20cant%20work%20it%20out!%3C%2FP%3E%3CP%3EThanks%20for%20any%20help%3C%2FP%3E%3CP%3EDino%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1872642%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1873034%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formulas%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1873034%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F864223%22%20target%3D%22_blank%22%3E%40Dinodog%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20your%20results%20are%20in%20B2%3AB30.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20C2%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(B2%26lt%3B%3D4%2C0%25%2CIF(B2%26lt%3B%3D8%2C50%25%2C100%25))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFill%20down%20to%20C30.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1873943%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formulas%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1873943%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F864223%22%20target%3D%22_blank%22%3E%40Dinodog%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINT((A1-0.5)%2F4)%2F2%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1889006%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formulas%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1889006%22%20slang%3D%22en-US%22%3EHi%2C%20thanks%20for%20the%20reply%2C%20but%20it%20has%20not%20worked.%3CBR%20%2F%3EI%20just%20get%20a%20value%20of%201%20in%20the%20cell%3CBR%20%2F%3EI%20need%20to%20go%20from%20C5%20to%20L5%3CBR%20%2F%3EIf%20i%20try%20to%20extend%20the%20firmula%20by%3A%3CBR%20%2F%3E%3DIF(C5%2CL5%26lt%3B4%2C0%25%2CIFC5%2CL5%26lt%3B8%2C50%25%2C100%25))%3CBR%20%2F%3EThat%20does%20not%20work%20either%3CBR%20%2F%3EAny%20help%20would%20be%20gratefully%20appreciated.%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1889007%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formulas%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1889007%22%20slang%3D%22en-US%22%3EHi%20this%20works%20for%20individual%20cells%2C%20but%20i%20need%20it%20to%20correlate%20and%20sum%20C5%20to%20L5%2C%20then%20C6%20to%20L6%2C%20and%20so%20on%3CBR%20%2F%3EAny%20more%20help%20is%20appreciated%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1889009%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formulas%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1889009%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F864223%22%20target%3D%22_blank%22%3E%40Dinodog%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(SUM(C5%3AL5)%26lt%3B4%2C0%25%2CIF(SUM(C5%3AL5)%26lt%3B8%2C50%25%2C100%25))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThis%20can%20be%20filled%20down.%3C%2FP%3E%0A%3CP%3EIf%20that%20does%20not%20do%20what%20you%20want%2C%20please%20explain%20more%20clearly%20what%20you%20want%20to%20accomplish.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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
Highlighted

@Dinodog 

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.

Highlighted

@Dinodog 

As variant

=INT((A1-0.5)/4)/2
Highlighted
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
Highlighted
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
Highlighted

@Dinodog 

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.

Highlighted

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

Highlighted

@Dinodog 

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

Highlighted
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
Highlighted

@Dinodog 

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.

Highlighted
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
Highlighted

@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.