Note when two values are too different.

[Tanquen]

I currently change the fill color based on the percentage of average.
Row 2 looks worse when looking at the percentage of average but they are not that different in absolute value.
While rows 3, 4 and 5 all have the same percentage of average, row 5 has a higher absolute difference.

I'm not sure there is a good way to do this but something like:
They are green when there is no difference.
Blue when the percentage of average is greater than 10.
Red when the percentage of average is greater than 10 and the absolute difference is greater than some percent of the largest of the two V1 and V2 values?

V1	V2	Percentage of their Average	Absolute Difference
1.4	1.9	30.3	0.5
50	60	18.2	10
10	12	18.2	2
1000	1200	18.2	200
59.4	59.35	0.1	0.05
83	82.96	0.0	0.04

 

[6StringJazzer]

What are you trying to evaluate?

Row 2 looks worse

Worse than what? What is good and what is bad? Are you trying to develop a measure of the spread between the two numbers, with a smaller spread being "better"? It would be helpful to understand the real-world application and how you will use the resulting information. There may be other statistical options depending on what you need to do.

 

[Tanquen]

"Looking for a good way to note when two values are too different."
"Row 2 looks worse when looking at the percentage of average but they are not that different in absolute value."

0 for both Percentage of their Average and Absolute Difference is best.

"While rows 3, 4 and 5 all have the same percentage of average, row 5 has a higher absolute difference."
So this mostly gets me what I want when looking the percentage of average, for rows 3, 4 and 5.

Looking at row 1, it's not as realistic to say because both numbers are small that the difference is more problematic than all others as it's at 30.3 percentage of average. They are more similar than most as the Absolute Difference is only 0.5.

V1 and V2 come from different sources that should match and when they don't, we need to make note. Maybe there is no good way to try and do this. I think the main issue is I don't have a min and max range for each pair of values. If the rage for row one is 0-100,000 gallons of water then the difference is nothing.

If I just made them red if the Absolute Difference was greater than 5, that works for row 2 but not row 5.
Row 2 - 1.4 vs 1.9 - Not that difference and 0.5 is less than 5, not red.
Row 5 - 1000 vs 1200 - Not that difference but 200 is more than 5, red.

 

[6StringJazzer]

To give you advice that has any value, I would need to understand the real-world problem you are trying to solve to understand the sensitivity to variations. What are you measuring and why do you care about the difference?

Looking at row 1, it's not as realistic to say because both numbers are small that the difference is more problematic than all others as it's at 30.3 percentage of average. They are more similar than most as the Absolute Difference is only 0.5.

The difference might be negligible if you are talking about the number of ounces of water in a 42-gallon barrel.

The difference is crucial if you are talking about milligrams of fentanyl.

 

[Tanquen]

Only have the numbers like I've show and as I said do not know the min and max range for them.

 

[6StringJazzer]

I'll ask again. What do the numbers mean? What are you measuring, and why are you trying to get a metric for the spread?

If you cannot answer those two questions, it's not possible to solve your problem, because "too different" depends completely on what they mean.

 

[Tanquen]

Thanks for trying. I've already answered those questions. I wish I had more info but I don't. I've already described as best I can a few different ways, but if it doesn't make any sense, I guess there's nothing that can be done.

 

[6StringJazzer]

You have not answered the questions at all. All I have is "these are numbers." You haven't said what they represent, what they measure, or why you care about the spread.

Your question is not really an Excel problem, it's a metrics problem, and there is not enough information to give you a better solution than what you started with.

 

[Tanquen]

I understand where you're coming from but I did answer the questions. I can't give you what I don't have. Just read through the posts again. I gave you all the information I have and told you that's all the information I have and I explained the differences that I was looking at and what I was trying to do more than once. I said more than once that there may not be a way to do anything better but I was just checking.