Decimal Places

[rmccain]

Hello to all and thank you for taking time to read this.

I have a simple formula that calculates 9 places to the right of the decimal point. Is there anyway to calculate only 2 places to the right of the decimal point without using the ROUND function ?

I ask this question because it takes ions to calculate several dozen worksheets with 10's of thousands of rows. If I only have 2 decimal places; i.e. 10.23 instead of 10.239870985 it would speed up the time it takes to compute the final answer.

I created a separate macro to round the answer to 2 decimal places and it takes foreverrrrrrrrrrrr. There must be an easier way.

Please Login or Register  to view this content.

Aloha To All

[kalbasiatka]

Do not write the formula, just calculate in the code
for example

Please Login or Register  to view this content.

even faster if to work with arrays and not with cells

[xladept]

You'd better protect the calculation by testing the B2 value!

Please Login or Register  to view this content.

[joeu2004]

I have a simple formula that calculates 9 places to the right of the decimal point. Is there anyway to calculate only 2 places to the right of the decimal point without using the ROUND function ?

I ask this question because it takes ions to calculate several dozen worksheets with 10's of thousands of rows. If I only have 2 decimal places; i.e. 10.23 instead of 10.239870985 it would speed up the time it takes to compute the final answer.
I created a separate macro to round the answer to 2 decimal places and it takes foreverrrrrrrrrrrr.

It does not take any more "ions" to calculate with many decimal places than to calculate with 2 decimal places.

I assume you mean that it takes extra CPU cycles to explicitly round formulas so the result is always 2 decimal places.

And yes, that can be very costly if you rely on event macros to adjust the calculation after-the-fact instead of using the Excel ROUND function.

(Of course, the Excel ROUND does also add CPU time. But not nearly as much as an event macro would.)

The only alternative is to set the option "Precision as displayed". But there are many reasons why many of us deprecate its use.

Caveat: If you choose to try setting PAD, be sure to make a copy of the Excel file first. Setting PAD can unintentionally change constants irreversibly.

Some of the many concerns about PAD:

1. "Precision as displayed" is a misnomer. It should be "Precision as formatted". The distinction is: if the cell is formatted as General and Excel chooses to display a result with only 2 decimal places, the actual underlying value might still have more decimal places, even if PAD is set. We must explicitly format cells with a specific number of decimal places in order for PAD to behave as intended.

2. PAD only applies to the final numeric result of a cell. So, for example, IF(10.1 - 10 = 0.1, TRUE) still returns FALSE(!).

3. PAD applies to all cells in all worksheets in a workbook. Consequently, setting PAD might affect other formulas unexpectedly, especially in non-active or hidden worksheets. If you set PAD after implementing a very complex workbook, it would behoove you to review every formula and cell format to be sure the results are what you intended.

4. PAD might change constants irreversibly. Suppose you want to enter an interest rate as Percentage with 6 decimal places for accuracy, but you format it as Percentage with 2 decimal places for readability. You cannot do that, if PAD is already set. And if you enter the accurate interest rate, then set PAD (or apply the format), the interest rate is changed permanently. That might affect directly and indirectly dependent calculations throughout the workbook.

5. When PAD is set, merely changing a cell's format (appearance) can unintentionally impact calculations in directly and indirectly dependent cells throughout the workbook.

Be that as it may, only you can weigh the risks of setting PAD and concomitant workbook-wide review with the effort to change "dozens" of worksheets with "10s of thousands" of rows.

As difficult as that might seem, often it is possible to insert the requisite ROUND functions, with due consideration, by selecting a range of cells, modifying the first formula, then pressing ctrl-Enter (not ctrl-shift-Enter) instead of Enter in order to propagate the change throughout the range.

Nevertheless, I agree that it can be a daunting task as an after-thought for such a complex workbook.

[rmccain]

Thank you for your reply.

Very interesting and I have read about this but not with a great explanation like yours. May, I ask you this;

I am running an i7 3610QM with 16GB of RAM . I also start the task manager and look at the processing power and it is not maxing out; it is maybe at 25% . Is there any other solutions ?

Thankyou Again

[rmccain]

Aha you are right , I was trying to figure out what the code was for this !!!

Thankyou .

[rmccain]

This is the actual code if I use the example above it will not calculate the cells independently. Every cells in A and B have different values.

LR = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("C2").Formula = "=(A2-B2)/ B2 * 100"
.Range("C2:L" & LR).FillDown

Thankyou