For Excel Ninjas Only --Plus anyone else who is REALLY up for a challenge... :)

[cubs610]

Hi All,


Ok, here is the deal. I'll be as short and succinct as I can. I need to find a way to change the core values of cells. For example, let's say I have 5 cells with the following values-- 23, 46, 67, 12 and 56, which add up to 204. Once we have the total, I now need to change the actual values so they equal 250. Next I have to spread the difference, or 46 (250-204) across the five cells.


I can use any combination of numbers to get to the target of 250. I can add 6 to the 23, leaving a balance of 40. Then add 15 to the 67, leaving 25 and so forth. Hopefully you get the point. It's tough to detail everything in a small amount of space.


My bet is the solution entails creating a macro. Problem is I'm not great with VBA. If anyone has any solutions, I'm all ears.


And in case you are wondering why this needs to be done. Thank one of our clients. They send us the raw numbers, want everything rounded (using Mround function) and create reports using the altered numbers. Yeah, I don't get it either...


Thanks!!!!

 

[JP]

A1:A5 contains your values: 23, 46, 67, 12, 56


This formula calculates how much you need to add to each value to reach 250:


=(250-SUM(A1:A5))/COUNTA(A1:A5)


Add this to each cell's value to get the new value, i.e. in cell B1 put "=A1+((250-SUM(A1:A5))/COUNTA(A1:A5))"


Does that work for you?

 

[vaskov17]

Paste the following into a module. To run it select the cells you want to change, run the macro and enter new sum when it asks for it.


Sub RangeChange()

Dim objCell As Range

Dim oldSum As Long

Dim newSum As Long


newSum = CLng(InputBox("Input sum"))


oldSum = 0


For Each objCell In Selection

oldSum = oldSum + objCell.Value

Next


For Each objCell In Selection

objCell.Value = objCell.Value / oldSum * newSum

Next


Set objCell = Nothing

End Sub

 

[Luke M]

You could also do this pretty quick using the Solver add-in.

Let's say the values are in A1:A5, and in A6 you have:

=SUM(A1:A5)


Start up the Solver, define target cell of A6, with goal of 250 (or whichever value you want).

Define A1:A5 as the cells it can change.

Add constraint, A1:A5 are integers (select from the dropdown).

Solve.


Chandoo wrote about Solver once:

http://chandoo.org/wp/2011/05/11/using-solver-to-assign-item/

 

[JP]

I like the Solver solution. Thought about that but went with formulas instead.

 

[cubs610]

Hi Guys, and thanks. I'll have to plug in some of these options and see what works.


@LukeM and JP-- I did initially consider the solver since I am familiar with the add-in. It could work but, the issue is I need to run this calculation for over 1000+ rows,all working out to various multiples of 150. And these calculations needs to be run every month in which the results eventually end up in a pivot table. (ugh...I know) So given this the solver can only take me so far...


Sorry I forgot to mention this in my previous post. Thank you for your efforts though--much appreciated.


-Dave

 

[dan_l]

A few things:


1. I don't like the cubs.

2. If I were to approach a problem like this, I would undoubtedly go with vba. Vaskov already posted a fairly simple way.

3. Your client might be strange.

 

[Hui]

If you are increasing each value by an equal percentage of its contribution to the inital Total I'd go down JP's orginal formula based approach

 

[Hui]

Cubs,

In the future can you also use a more descriptive Subject Line, like: "Changing a range of values that sum to equal a Target Value", rather than "For Excel Ninjas Only ..."


Thanx

 

[cubs610]

Hi Guys,


Thanks for the additional support.


@ Dan_l -- agreed. I do not like the Cubs either... Cubs my nickname and yes the client demands transcend ridiculous. You really don't want to know the hoops they have us jump through...not pretty.


@Hui-- understood and will do.


I'm going to play with both solutions, JP's and Vaskov's to see which is best.


-Dave