Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
turning text into a calculated result: | Excel Discussion (Misc queries) | |||
Modify a calculated field formula depending upon selection of pagefield | Excel Discussion (Misc queries) | |||
PivotTable:Using a calculated field result in another calculated f | Excel Worksheet Functions | |||
Shifting Calculated Result - Re-Issued | Excel Worksheet Functions | |||
Shifting Calculated Result | Excel Worksheet Functions |