Share via

Average value of Excel cells containing text that are the subject of individual weightings

Anonymous
2025-05-08T06:31:32+00:00

Hi Community,

I have a scenario whereby I have a column of 7 Excel text cells (let's define them as D4: D10). Each cell is driven by an Excel formula that will generate an outcome of either "A", "B" or "C". Please note, the results in D4:D10 will change depending upon the source data for each matter that is the subject of the Excel spreadsheet.

With respect to D4:D10, each outcome of each text cell is "weighted" in the following manner: 30%, 35%, 10%, 5%, 5%, 5%, 10% . Understandably, these weightings add up to 100%.

I would like to know whether there is an Excel formula that will generate an "average" text cell value, based upon the weightings of D4:D10. For this scenario, the "average" text cell value will generate to, say, D12.

For example, due to D4 and D5 having a combined "weighting" of 65%, if both text cell values were "C" it is most likely the "average" text cell value will be "C".

Trust that this makes sense - thanks in advance for the Community's assistance.

Microsoft 365 and Office | Excel | For business | Android

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Accepted answer
  1. HansV 460.7K Reputation points MVP Volunteer Moderator
    2025-05-08T08:23:31+00:00

    You might use

    =CHAR(ROUND(SUMPRODUCT(CODE(D4:D10), {0.3;0.35;0.1;0.05;0.05;0.05;0.1}), 0))

    or better, if the weights are entered in a range, say W4:W10:

    =CHAR(ROUND(SUMPRODUCT(CODE(D4:D10), W4:W10), 0))

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-05-08T08:20:00+00:00

    Hi Scott

    Try the formula =INDEX(GROUPBY(D4:D10,C4:C10,SUM,0,0,-2),1)

    I hope this helps you and gives a solution to your question.

    Regards

    Jeovany

    Image

    0 comments
  2. Anonymous
    2025-05-08T19:24:19+00:00

    Hi HansV, the first Excel formula works for me. Thank you very much for your assistance.

    0 comments