Share via

Comparing a value to an average of values by category

Anonymous
2024-10-21T14:14:53+00:00

For a sports simulation game, I have teams that have been assigned a prestige value. Teams belong to a variety of conference. I have calculated a weighted average of prestige for each conference. I would now like to use a formula to identify teams whose individual prestige value is greater than or equal to +/- 3 of their respective conference's weighted average. What would be the simplest way to do this?

Microsoft 365 and Office | Excel | Other | Windows

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

Answer accepted by question author

  1. Anonymous
    2024-10-21T15:59:33+00:00

    Hi John McLaughlin6,

    I'm glad to answer your question again.

    First, I need to confirm if you mean that you need to identify the team names whose "prestige value" has an absolute difference of greater than or equal to 3 (either too high or too low) compared to the weighted average. If my understanding is incorrect, please feel free to correct me in your reply.

    If this is the case, you can use conditional formatting combined with the VLOOKUP function to identify them. Here's how:

    First, I noticed that your data seems a bit unorganized. If possible, make sure all data starts from the second row, with the first row as the label row.

    After adjusting the data, let's assume your data fits the following simple format (I tried to create a simple test copy to match your specific data situation).

    Image

    Next, we can select column A (team names), click Home - Conditional Formatting - New Rule - Use a formula to determine which cells to format.

    Image

    In the formula bar, enter the following formula: 

    =ABS(P1-VLOOKUP(B1,V:X,3))>=3

    This formula will calculate the absolute difference between each team's prestige value and their corresponding conference's average value and find those greater than or equal to 3.

    Image

    After entering the formula, click Format..., where you can set how you want to identify these teams. For example, fill the cell with green (click Fill to choose the color).

    After that, double-check the conditions and range of the conditional formatting, confirm, and click OK.

    This way, the teams that meet the criteria will be clearly identified in the table.

    Image

    You can share your results with me. If you encounter any problems, feel free to reply.

    Best Regards,

    Jonathan Z - MSFT | Microsoft Community Support Specialist

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-10-21T16:38:51+00:00

    I actually figured that part out myself. Thank you so much, again. You are awesome

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-10-21T16:26:16+00:00

    This is awesome! Again!

    I do have one follow-up question. This is going off absolute value, so it's identifying both those +3 (or more) and -3 (or more) the same. Is there a way I could have it do +3 (or more) one color and -3 (or more) another color? If not, I can totally work with this lol, you've been a tremendous help and I've learned a lot!

    Was this answer helpful?

    0 comments