Solver Optimization issue

Hi all,

I am trying to figure out how to solve a problem, and would greatly appreciate your help. I have a list of past month to month sales variances that I have to report on, depending on certain threshold (ie. if the variance was +/- 5 from zero, I would have to write a comment to explain that change). To minimize work, I want to know (using Solver, I assume) how to find out the highest possible threshold (thus, the least amount of comments required) that gets me as close as possible to the total sum variance over the course of the past years (month over month sales variances). So essentially, I want to be able to capture as much of the total variance as possible, but want to have to put in the fewest amount of reports.

Let's say we have a list of variances:

-1, 8, 10, 42, -3, -5, -5, -9, 0, 2, -17, 9, etc.

The total sum of all the variances comes to, say, -12.

Now, for any variance over 5 or under -5, I have to write a report explaining why it happened. So I want to limit this number (# of reports) but get the sum of those variances reported on (which in the example would be 8, 10, 42, -9, -17, 9, summing to 43) to be as close to the total variance as possible. Obviously I have a lot more data than just a few numbers.

I am assuming I'll have to incorporate a sumif equation, totaling the numbers above or below the cutoff, and I just assumed solver would be the best way to go. I've been tripped up with the numbers, since they are both positive and negative, but assume there is a way around this. If anyone can shed a little light on how to begin on this, I would appreciate it. I've used a lot of excel, but it's these type of problems that tend to trip me up.

Thank you very much for any help you can offer.

Hello Angusbeef and welcome to the best Excel help forum on the web. If you want help from this site you have to read the Forum Rules. You can read them by clicking on the button right next to the Log Out button. Specifically, you need to read:

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum:

Once you read the rules and make the appropriate changes, someone will be able to help you.

Cheers

I'm unsure of your question. If it's how to arrive simply at the 43 number,

=SUMPRODUCT((ABS(A1:A12)>5) * A1:A12)