Displaying Significant Figures Dynamically

Copper Contributor

I have a large workbook with many formulas in it.  Users will enter the desired number of significant digits.   I know how to compute the number to the chosen number of significant digits (I found that formula online).  But now I need to figure out how to display the result to the number of significant digits chosen by the user, which will change depending on the user.

3 Replies
In excel you have controls on how to DISPLAY data and how to CALCULATE data. Proper use of significant digits (if my memory serves me from decades ago) says you should use the FULL numerical value throughout all calculations and only perform the rounding to correct significant digits for the final answer. That is ideal for the DISPLAY controls since that does not affect the actual value of the cell and only how it is displayed BUT those are not dynamically controllable without the use of a macro/VBA. An alternative solution is to have Calculation cells and Output cells where the Output cells refer to the final calculation of interest with a =ROUND(cell, sig dig) to round that answer to the proper number of significant digits. I suggest having both because often you have some outputs that may refer to previous outputs.
That said, my understanding is that in the world of finance they do NOT act that way and either ALL numbers are rounded to nearest 0.01 or ALL numbers are rounded to nearest $1 and partial values should not be carried.
I want to limit the DISPLAY to the number of significant digits specified by the user, which will be dynamic. Are you saying the only way to get that desired outcome is with VBA?