"Rounding" error

[Stuart]

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.

 

[y]

You could apply the ROUND statement for the sum.

round (sum();2)

I didn't test it.
Let us know.

Ciao Alex.

 

[Stuart]

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]

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.

 

[Stuart]

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.

 

[Tom Ogilvy]

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.

 

[Stuart]

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.

 

[y]

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.

 

[Tom Ogilvy]

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.

 

[Stuart]

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.

 

[y]

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]

Finally got there:
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.

 

[Maurizio Borrelli]

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