| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Rounding" error
Here is the data:
C F I J N O 1.17 4.45 2.79 7.24 8.47 8.47 1.23 2.12 2.12 2.61 2.61 7.81 7.81 is the sum of values in col F multiplied by their corresponding value in col C. So 7.81 should equal (1.17 * 4.45) + (1.23 * 2.12) ie 5.2065 + 2.6076 = 7.8141 round to 7.81 However, I need each multiplication to round. So 1.17 * 4.45 = 5.2065 = 5.21 and 1.23 * 2.12 = 2.6076 = 2.61 thus 5.21 + 2.61 = 7.82 Here's the code that currently sums the column: For ColNdx = 5 To 9 'cols E to I Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = "=SUM((" & Range( _ Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2, "C")) _ .Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "))" Cells(EndToCollectionRow - 1, ColNdx).Value = _ Cells(EndToCollectionRow - 1, ColNdx).Text Next ColNdx Is there a way to modify the code, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Rounding" error
You could apply the ROUND statement for the sum.
round (sum();2) I didn't test it. Let us know. Ciao Alex. Stuart wrote: Here is the data: C F I J N O 1.17 4.45 2.79 7.24 8.47 8.47 1.23 2.12 2.12 2.61 2.61 7.81 7.81 is the sum of values in col F multiplied by their corresponding value in col C. So 7.81 should equal (1.17 * 4.45) + (1.23 * 2.12) ie 5.2065 + 2.6076 = 7.8141 round to 7.81 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Rounding" error
Thanks, but I think the chance to Round has passed by the time
the value hits the Sum cell. I guess I need to find a way to Round each calculation BEFORE its' result is added to the Sum....if you see what I mean. Regards "y" wrote in message ... You could apply the ROUND statement for the sum. round (sum();2) I didn't test it. Let us know. Ciao Alex. Stuart wrote: Here is the data: C F I J N O 1.17 4.45 2.79 7.24 8.47 8.47 1.23 2.12 2.12 2.61 2.61 7.81 7.81 is the sum of values in col F multiplied by their corresponding value in col C. So 7.81 should equal (1.17 * 4.45) + (1.23 * 2.12) ie 5.2065 + 2.6076 = 7.8141 round to 7.81 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Rounding" error
Stuart wrote:
Thanks, but I think the chance to Round has passed by the time the value hits the Sum cell. I guess I need to find a way to Round each calculation BEFORE its' result is added to the Sum....if you see what I mean. Sorry Stuart, round globally the product this way .....formularray = SUM(ROUND(array1*array2;roundoff)) Choose the round off you desire. I tested this formula manually in a worksheeet and Excel allows it. Let us know. Alex. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Rounding" error
Have amended to:
For ColNdx = 5 To 9 'cols E to I Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = "=SUM(ROUND(" & Range( _ Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2, "C")) _ .Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2)" Cells(EndToCollectionRow - 1, ColNdx).Value = _ Cells(EndToCollectionRow - 1, ColNdx).Text Next ColNdx and get "Unable to set the FormulaArray property of the Range class" error. Did I miscode your suggestion? Regards and thanks. "y" wrote in message ... Stuart wrote: Thanks, but I think the chance to Round has passed by the time the value hits the Sum cell. I guess I need to find a way to Round each calculation BEFORE its' result is added to the Sum....if you see what I mean. Sorry Stuart, round globally the product this way ....formularray = SUM(ROUND(array1*array2;roundoff)) Choose the round off you desire. I tested this formula manually in a worksheeet and Excel allows it. Let us know. Alex. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Rounding" error
What kind of testing have you done Stuart?
Sub Tester9() starttocollectionRow = 1 Endtocollectionrow = 8 colndx = 3 sStr = "=SUM(ROUND(" & Range( _ Cells(starttocollectionRow, "C"), Cells(Endtocollectionrow - 2, "C")) _ .Address & ")*(" & Range(Cells(starttocollectionRow, colndx), _ Cells(Endtocollectionrow - 2, colndx)).Address & "), 2)" Debug.Print sStr End Sub produces =SUM(ROUND($C$1:$C$6)*($C$1:$C$6), 2) so you have clearly miscoded it since this formula is in error. -- Regards, Tom Ogilvy "Stuart" wrote in message ... Have amended to: For ColNdx = 5 To 9 'cols E to I Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = "=SUM(ROUND(" & Range( _ Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2, "C")) _ .Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2)" Cells(EndToCollectionRow - 1, ColNdx).Value = _ Cells(EndToCollectionRow - 1, ColNdx).Text Next ColNdx and get "Unable to set the FormulaArray property of the Range class" error. Did I miscode your suggestion? Regards and thanks. "y" wrote in message ... Stuart wrote: Thanks, but I think the chance to Round has passed by the time the value hits the Sum cell. I guess I need to find a way to Round each calculation BEFORE its' result is added to the Sum....if you see what I mean. Sorry Stuart, round globally the product this way ....formularray = SUM(ROUND(array1*array2;roundoff)) Choose the round off you desire. I tested this formula manually in a worksheeet and Excel allows it. Let us know. Alex. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Rounding" error
I see what you mean Tom.
Nevertheless, I cannot see the reason for that original error. The original working code sequence was: For ColNdx = 5 To 9 'cols E to I Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = "=SUM((" & Range( _ Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2, "C")) _ .Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "))" Cells(EndToCollectionRow - 1, ColNdx).Value = _ Cells(EndToCollectionRow - 1, ColNdx).Text Next ColNdx which produced that error of which I originally posted. With Alex's help I tried to implement the Round function within the above FormulaArray expression, as follows: For ColNdx = 5 To 9 'cols E to I Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = "=SUM(ROUND(" & Range( _ Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2, "C")) _ .Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2)" Cells(EndToCollectionRow - 1, ColNdx).Value = _ Cells(EndToCollectionRow - 1, ColNdx).Text Next ColNdx Can this be achieved, please? Regards. "Tom Ogilvy" wrote in message ... What kind of testing have you done Stuart? Sub Tester9() starttocollectionRow = 1 Endtocollectionrow = 8 colndx = 3 sStr = "=SUM(ROUND(" & Range( _ Cells(starttocollectionRow, "C"), Cells(Endtocollectionrow - 2, "C")) _ .Address & ")*(" & Range(Cells(starttocollectionRow, colndx), _ Cells(Endtocollectionrow - 2, colndx)).Address & "), 2)" Debug.Print sStr End Sub produces =SUM(ROUND($C$1:$C$6)*($C$1:$C$6), 2) so you have clearly miscoded it since this formula is in error. -- Regards, Tom Ogilvy "Stuart" wrote in message ... Have amended to: For ColNdx = 5 To 9 'cols E to I Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = "=SUM(ROUND(" & Range( _ Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2, "C")) _ .Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2)" Cells(EndToCollectionRow - 1, ColNdx).Value = _ Cells(EndToCollectionRow - 1, ColNdx).Text Next ColNdx and get "Unable to set the FormulaArray property of the Range class" error. Did I miscode your suggestion? Regards and thanks. "y" wrote in message ... Stuart wrote: Thanks, but I think the chance to Round has passed by the time the value hits the Sum cell. I guess I need to find a way to Round each calculation BEFORE its' result is added to the Sum....if you see what I mean. Sorry Stuart, round globally the product this way ....formularray = SUM(ROUND(array1*array2;roundoff)) Choose the round off you desire. I tested this formula manually in a worksheeet and Excel allows it. Let us know. Alex. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Rounding" error
Hi Stuart,
I tried your expression and for the 1st step it assumes "=SUM(ROUND($C$1)*($E$1), 2)" I think it is not correct because you want to obtain this formula: =SUM(ROUND(array1*array2,2)) (**) So 1) declare a sTmp (string) variable. 2) modify the For..next statements this way: sTmp = "=SUM(ROUND(" & Range( _ Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2, "C")) _ .Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2)" Cells(EndToCollectionRow - 1, ColNdx).Value = sTmp Note I added an instruction in loop 3) add a control to sTmp variable and control step by step if the sTmp assumes the correct formula. Alternative is to insert this line of code in the loop MsgBox sTmp that display to you what Cells(EndToCollectionRow - 1, ColNdx).Value will be set to 4) adjust the "=SUM(ROUND.... to obtain the formula (**) 5) now it works ( ...I hope ;) ) I suggest to you to start from the final formula (id est (**) ), then substitute all fixed values with the parameters/formulas that returns that fixed values. 1 step ) =SUM(ROUND(array1*array2,2)) 2 step ) "=SUM(ROUND(" & array1*array2,2)) 3 step ) "=SUM(ROUND(" & array1*array2 & ",2))" and then the expression for array1*array2 another help is to use substrings and then concatenate them. It would reduce errors in exploding the expression. So sTmp1 = "=SUM(ROUND(" .... sTmp.. = ",2))" and finally Cells(EndToCollectionRow - 1, ColNdx).Value = sTmp & ... & sTmp.. Hope this helps. Alex. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Rounding" error
Sub Tester9()
starttocollectionRow = 1 Endtocollectionrow = 8 colndx = 5 sStr = "=SUM(ROUND((" & Range( _ Cells(starttocollectionRow, "C"), Cells(Endtocollectionrow - 2, "C")) _ .Address & ")*(" & Range(Cells(starttocollectionRow, colndx), _ Cells(Endtocollectionrow - 2, colndx)).Address & "), 2))" Debug.Print sStr End Sub returns =SUM(ROUND(($C$1:$C$6)*($E$1:$E$6), 2)) Which appears to be something like what you want. It is a legal formula at least. -- Regards, Tom Ogilvy "Stuart" wrote in message ... I see what you mean Tom. Nevertheless, I cannot see the reason for that original error. The original working code sequence was: For ColNdx = 5 To 9 'cols E to I Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = "=SUM((" & Range( _ Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2, "C")) _ .Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "))" Cells(EndToCollectionRow - 1, ColNdx).Value = _ Cells(EndToCollectionRow - 1, ColNdx).Text Next ColNdx which produced that error of which I originally posted. With Alex's help I tried to implement the Round function within the above FormulaArray expression, as follows: For ColNdx = 5 To 9 'cols E to I Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = "=SUM(ROUND(" & Range( _ Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2, "C")) _ .Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2)" Cells(EndToCollectionRow - 1, ColNdx).Value = _ Cells(EndToCollectionRow - 1, ColNdx).Text Next ColNdx Can this be achieved, please? Regards. "Tom Ogilvy" wrote in message ... What kind of testing have you done Stuart? Sub Tester9() starttocollectionRow = 1 Endtocollectionrow = 8 colndx = 3 sStr = "=SUM(ROUND(" & Range( _ Cells(starttocollectionRow, "C"), Cells(Endtocollectionrow - 2, "C")) _ .Address & ")*(" & Range(Cells(starttocollectionRow, colndx), _ Cells(Endtocollectionrow - 2, colndx)).Address & "), 2)" Debug.Print sStr End Sub produces =SUM(ROUND($C$1:$C$6)*($C$1:$C$6), 2) so you have clearly miscoded it since this formula is in error. -- Regards, Tom Ogilvy "Stuart" wrote in message ... Have amended to: For ColNdx = 5 To 9 'cols E to I Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = "=SUM(ROUND(" & Range( _ Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2, "C")) _ .Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2)" Cells(EndToCollectionRow - 1, ColNdx).Value = _ Cells(EndToCollectionRow - 1, ColNdx).Text Next ColNdx and get "Unable to set the FormulaArray property of the Range class" error. Did I miscode your suggestion? Regards and thanks. "y" wrote in message ... Stuart wrote: Thanks, but I think the chance to Round has passed by the time the value hits the Sum cell. I guess I need to find a way to Round each calculation BEFORE its' result is added to the Sum....if you see what I mean. Sorry Stuart, round globally the product this way ....formularray = SUM(ROUND(array1*array2;roundoff)) Choose the round off you desire. I tested this formula manually in a worksheeet and Excel allows it. Let us know. Alex. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Rounding" error
Ok. In my situation I now have
sStr = "=SUM(ROUND(" & Range(Cells(StartToCollectionRow, "C"), _ Cells(EndToCollectionRow - 2, "C")).Address & ") * (" & Range _ (Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2)" Debug.Print sStr which gives "=SUM(ROUND($C$7:$C$40) * ($E$7:$E$40), 2)" How do I return that result to the range Cells(EndToCollectionRow - 1, ColNdx) , please? Regards. "Tom Ogilvy" wrote in message ... Sub Tester9() starttocollectionRow = 1 Endtocollectionrow = 8 colndx = 5 sStr = "=SUM(ROUND((" & Range( _ Cells(starttocollectionRow, "C"), Cells(Endtocollectionrow - 2, "C")) _ .Address & ")*(" & Range(Cells(starttocollectionRow, colndx), _ Cells(Endtocollectionrow - 2, colndx)).Address & "), 2))" Debug.Print sStr End Sub returns =SUM(ROUND(($C$1:$C$6)*($E$1:$E$6), 2)) Which appears to be something like what you want. It is a legal formula at least. -- Regards, Tom Ogilvy "Stuart" wrote in message ... I see what you mean Tom. Nevertheless, I cannot see the reason for that original error. The original working code sequence was: For ColNdx = 5 To 9 'cols E to I Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = "=SUM((" & Range( _ Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2, "C")) _ .Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "))" Cells(EndToCollectionRow - 1, ColNdx).Value = _ Cells(EndToCollectionRow - 1, ColNdx).Text Next ColNdx which produced that error of which I originally posted. With Alex's help I tried to implement the Round function within the above FormulaArray expression, as follows: For ColNdx = 5 To 9 'cols E to I Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = "=SUM(ROUND(" & Range( _ Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2, "C")) _ .Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2)" Cells(EndToCollectionRow - 1, ColNdx).Value = _ Cells(EndToCollectionRow - 1, ColNdx).Text Next ColNdx Can this be achieved, please? Regards. "Tom Ogilvy" wrote in message ... What kind of testing have you done Stuart? Sub Tester9() starttocollectionRow = 1 Endtocollectionrow = 8 colndx = 3 sStr = "=SUM(ROUND(" & Range( _ Cells(starttocollectionRow, "C"), Cells(Endtocollectionrow - 2, "C")) _ .Address & ")*(" & Range(Cells(starttocollectionRow, colndx), _ Cells(Endtocollectionrow - 2, colndx)).Address & "), 2)" Debug.Print sStr End Sub produces =SUM(ROUND($C$1:$C$6)*($C$1:$C$6), 2) so you have clearly miscoded it since this formula is in error. -- Regards, Tom Ogilvy "Stuart" wrote in message ... Have amended to: For ColNdx = 5 To 9 'cols E to I Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = "=SUM(ROUND(" & Range( _ Cells(StartToCollectionRow, "C"), Cells(EndToCollectionRow - 2, "C")) _ .Address & ")*(" & Range(Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2)" Cells(EndToCollectionRow - 1, ColNdx).Value = _ Cells(EndToCollectionRow - 1, ColNdx).Text Next ColNdx and get "Unable to set the FormulaArray property of the Range class" error. Did I miscode your suggestion? Regards and thanks. "y" wrote in message ... Stuart wrote: Thanks, but I think the chance to Round has passed by the time the value hits the Sum cell. I guess I need to find a way to Round each calculation BEFORE its' result is added to the Sum....if you see what I mean. Sorry Stuart, round globally the product this way ....formularray = SUM(ROUND(array1*array2;roundoff)) Choose the round off you desire. I tested this formula manually in a worksheeet and Excel allows it. Let us know. Alex. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Rounding" error
An italian MVP suggest to me this way:
replace the Cells(EndToCollectionRow - 1, ColNdx).FormulaLocal = ... with Cells(EndToCollectionRow - 1, ColNdx).Formulalocal = ... With Cells(ETCR - 1, ColNdx) .FormulaArray = .Formula End With I tried it and it works. Hoping to hear you about other questions ;) I don't know why you must pass through .Formulalocal Ciao Alex. Stuart wrote: Ok. In my situation I now have sStr = "=SUM(ROUND(" & Range(Cells(StartToCollectionRow, "C"), _ Cells(EndToCollectionRow - 2, "C")).Address & ") * (" & Range _ (Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2)" Debug.Print sStr which gives "=SUM(ROUND($C$7:$C$40) * ($E$7:$E$40), 2)" How do I return that result to the range Cells(EndToCollectionRow - 1, ColNdx) , please? Regards. |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Rounding" error
Finally got the
For ColNdx = 5 To 9 'cols E to I .Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = _ "=SUM(ROUND((" & .Range(.Cells(StartToCollectionRow, "C"), _ .Cells(EndToCollectionRow - 2, "C")).Address & ") * (" & _ .Range(.Cells(StartToCollectionRow, ColNdx), _ .Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2))" .Cells(EndToCollectionRow - 1, ColNdx).Value = _ .Cells(EndToCollectionRow - 1, ColNdx).Text Next ColNdx Thanks both for the help. Regards. "y" wrote in message ... An italian MVP suggest to me this way: replace the Cells(EndToCollectionRow - 1, ColNdx).FormulaLocal = ... with Cells(EndToCollectionRow - 1, ColNdx).Formulalocal = ... With Cells(ETCR - 1, ColNdx) .FormulaArray = .Formula End With I tried it and it works. Hoping to hear you about other questions ;) I don't know why you must pass through .Formulalocal Ciao Alex. Stuart wrote: Ok. In my situation I now have sStr = "=SUM(ROUND(" & Range(Cells(StartToCollectionRow, "C"), _ Cells(EndToCollectionRow - 2, "C")).Address & ") * (" & Range _ (Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2)" Debug.Print sStr which gives "=SUM(ROUND($C$7:$C$40) * ($E$7:$E$40), 2)" How do I return that result to the range Cells(EndToCollectionRow - 1, ColNdx) , please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Rounding" error
Ciao, Stuart.
I've read about your question on mpioe only what said us "y", not from the beginning of thread. Now I've read the true question and I think that a loop is not necessary. As you know, in Excel you can enter a formula on multiple cells: selecting the cells writing the formula then ending the input with Ctrl+Enter Try so, then try it recording a macro. So I think you can write your macro also: With Range("E5:I5") .FormulaR1C1 = "=ROUND(SUMPRODUCT(R1C3:R2C3,R[-4]C:R[-3]C),2)" .Value = .Value End With -- Ciao :o) Maurizio Borrelli, Microsoft MVP - Office System - Access -------- ?SPQR(C) X -------- "Stuart" ha scritto nel messaggio ... Finally got the For ColNdx = 5 To 9 'cols E to I .Cells(EndToCollectionRow - 1, ColNdx).FormulaArray = _ "=SUM(ROUND((" & .Range(.Cells(StartToCollectionRow, "C"), _ .Cells(EndToCollectionRow - 2, "C")).Address & ") * (" & _ .Range(.Cells(StartToCollectionRow, ColNdx), _ .Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2))" .Cells(EndToCollectionRow - 1, ColNdx).Value = _ .Cells(EndToCollectionRow - 1, ColNdx).Text Next ColNdx Thanks both for the help. Regards. "y" wrote in message ... An italian MVP suggest to me this way: replace the Cells(EndToCollectionRow - 1, ColNdx).FormulaLocal = ... with Cells(EndToCollectionRow - 1, ColNdx).Formulalocal = ... With Cells(ETCR - 1, ColNdx) .FormulaArray = .Formula End With I tried it and it works. Hoping to hear you about other questions ;) I don't know why you must pass through .Formulalocal Ciao Alex. Stuart wrote: Ok. In my situation I now have sStr = "=SUM(ROUND(" & Range(Cells(StartToCollectionRow, "C"), _ Cells(EndToCollectionRow - 2, "C")).Address & ") * (" & Range _ (Cells(StartToCollectionRow, ColNdx), _ Cells(EndToCollectionRow - 2, ColNdx)).Address & "), 2)" Debug.Print sStr which gives "=SUM(ROUND($C$7:$C$40) * ($E$7:$E$40), 2)" How do I return that result to the range Cells(EndToCollectionRow - 1, ColNdx) , please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "num#" errors .. how to average a group with a "num#" error | Excel Discussion (Misc queries) | |||
| "Document not saved" "error in loading DLL" | Excel Discussion (Misc queries) | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| Shortcut key for "Paste Options" and "Error Checking" buttons? | Excel Discussion (Misc queries) | |||
![Stuart[_5_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
