Highlight/Select 'x' number of cells that average 'y'

[Dual Showman]

I need assistance with a formula or function that will select or highlight or otherwise distinguish 'X' number of cells in a range that, when averaged, equals 'Y'. I feel like there is a very clean way of accomplishing this, but my brain is cooked.

 

[6StringJazzer]

This is actually a difficult problem. The number of combinations that has to be checked increases exponentially with the number of values. This gets very big very fast. My first attempt at this would be using Solver. How many total values do you have and what is a typical X? Do you have some sample data you can provide for testing? Another way would be to exhaustively test all combinations using VBA--again, very big.

 

[Dual Showman]

Thank you for your reply.

Yeah, I was trying to avoid VBA in order for it to be more user-serviceable once I'm not around to troubleshoot the non-Excel parts of the application, although VBA could solve all of this here given sufficient effort on my part. Too many irons in the fire. I'll look into Solver. No experience there.

To answer your question: Total values would ideally 100 plus, but let's say it's 40. X is typically 5 or 10. The sample data can be random numbers from -2 to 2.

 

[6StringJazzer]

I can put together an example for you. What is Y?

-2 to 2 is a narrow range--are these real numbers or integers? If they are real numbers it complicates the problem due to real-number computer arithmetic.

 

[Dual Showman]

I can put together an example for you. What is Y?

-2 to 2 is a narrow range--are these real numbers or integers? If they are real numbers it complicates the problem due to real-number computer arithmetic.

They're real numbers, but for ease we can make it -200 to 200. Let's make Y = 2.