Modify calculated result?
 

Excel 2002, Windows 2000 SP4

I have a simple formula that takes a number in column A divided by a
constant cell in Column B to return a number in Column C and round to one
decimal point (ROUND(A3/$B$2,1)).

Then, I sum the results (Column 3) to come up with a number (=SUM(C3:C6)).
So far so good.

Problem: I need the Sum of Column 3 to ALWAYS equal 40. This may entail
changing one or more entries in Column C (or maybe an interim column if
necessary) by a tenth-place decimal +/- to come up with 40.

Any suggestions for how to do this? I'd prefer a formula, but will do VBA
if necessary. Thank you for your help.

Modify calculated result?
 

Hi Allison,

1 2.666666667 =A2 * 40 / SUM(A$2:A$5)
2 5.333333333 =A3 * 40 / SUM(A$2:A$5)
4 10.66666667 =A4 * 40 / SUM(A$2:A$5)
8 21.33333333 =A5 * 40 / SUM(A$2:A$5)
----- -----
15 40 =SUM(B2:B5)

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Allison" wrote in message ...
Excel 2002, Windows 2000 SP4

I have a simple formula that takes a number in column A divided by a
constant cell in Column B to return a number in Column C and round to one
decimal point (ROUND(A3/$B$2,1)).

Then, I sum the results (Column 3) to come up with a number (=SUM(C3:C6)).
So far so good.

Problem: I need the Sum of Column 3 to ALWAYS equal 40. This may entail
changing one or more entries in Column C (or maybe an interim column if
necessary) by a tenth-place decimal +/- to come up with 40.

Any suggestions for how to do this? I'd prefer a formula, but will do VBA
if necessary. Thank you for your help.