Forum Discussion

Dinodog's avatar
Dinodog
Copper Contributor
Nov 10, 2020

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 result...
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Nov 19, 2020

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.

Dinodog's avatar
Dinodog
Copper Contributor
Nov 19, 2020
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
  • mtarler's avatar
    mtarler
    Silver Contributor
    Nov 19, 2020

    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.

Resources