If you are searching for the solution or some special tricks to round to significant figures in Excel then you have landed in the right place. There are several ways to round up significant figures in Excel. This article will show you each and every step with proper illustrations so you can easily apply them for your purpose. Let’s get into the central part of the article.
Suppose, you have a dataset where you have some sales amount of 7 digits and you want to round these values to certain significant figures. In this section, I will show you 3 quick and easy methods to round to significant figures in Excel on Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If anything of this article doesn’t work in your version then leave us a comment.
1. Using Excel Formula Round to Significant Figures
1.1 Combining ROUND, INT, LOG10 & ABS Functions
You can create an Excel formula using the ROUND function with the INT function, LOG10 function, and ABS function. Follow the steps below to use this formula to round the sales values to a certain significant figure.
📌 Steps:
- First, paste the following formula into cell E5 to round the value of cell D5 to 3 significant digits.
=ROUND(D5,3-(1+INT(LOG10(ABS(D5)))))🔎 Formula Breakdown:
Here, showing the breakdown of the formula for the cell E5:
- D5 = 7611250: It’s the cell value that will be rounded to significant figures.
- ABS(D5) = ABS(7611250) = 7611250: The ABSOLUTE function will return a value in positive sign.
- LOG10(ABS(D5)) = LOG10(7611250) = 6.88145: The LOG10 function will return the 10 based logarithmic result of 7611250 which 6.88145.
- INT(LOG10(ABS(D5))) = INT(6.88145) = 6: The INT function will retire the nearest lower integer of value 6.88145 which is 6.
- 3-(1+INT(LOG10(ABS(D5))) = 3-(1+6) = -4: It gives the remaining digits of the number excluding the first 3 digits.
- ROUND(D5,3-(1+INT(LOG10(ABS(D5))))) = ROUND(7611250,-4) = 7610000: The ROUND function will round the last 4 digits to zero of the value.
- Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.
- As a result, you will get the values of the Sales column rounded to 2 significant
Read More: How to Keep Significant Figures in Excel
1.2 Using Excel Formula with ROUND, INT & LOG10 Functions to Round to Significant Figures
You can create another formula to round values to significant figures by specifying the significant figures in a cell. So, you can easily change the number of significant figures and make a dynamic worksheet. For this, follow the steps below:
📌 Steps:
- First, create a cell to take the input of significant figures.
- Then, paste the following formula into cell E5:
=ROUND(D5/10^(INT(LOG10(D5))+1),$G$5)*10^(INT(LOG10(D5))+1)🔎 Formula Breakdown:
Here, showing the breakdown of the formula for the cell E5:
- D5 = 7611250: It’s the cell value that will be rounded to significant figures.
- LOG10(D5) = LOG10(7611250) = 6.88145: The LOG10 function will return the 10-based logarithmic result of 7611250 which is 6.88145.
- INT(LOG10(D5))) = INT(6.88145) = 6: The INT function will retire the nearest lower integer of value 6.88145 which is 6.
- D5/10^(INT(LOG10(D5))+1) = 7611250/10^(6+1) = 0.761125: It converts the D5 cell value to a fraction.
- ROUND(D5/10^(INT(LOG10(D5))+1),$G$5) = ROUND(0.761125,4) = 0.761: Now, the ROUND function will round the fraction number to 3 digits after the decimal point.
- 10^(INT(LOG10(D5))+1) = 10^(6+1) = 10000000: Then, you have to multiply the rounded fraction value with this.
- ROUND(D5/10^(INT(LOG10(D5))+1),$G$5)*10^(INT(LOG10(D5))+1) =0.7611*10000000 = 7610000: This is the rounded value to 3 significant figures of cell D5
- Then, drag the fill handle icon to the last cell of the column and get the column filled with the output of the same formula.
- As a result, the Sales Column values are rounded to 3 significant figures.
- Now, change the value of the significant figures in cell G5 to 4 and you will see the Rounded Figure column is changed automatically to 4 significant figures.
Read More: How to Change Significant Figures in a Graph in Excel
2. Applying VBA Custom Function to Round to Significant Figures
You can also use VBA code to create a custom function to round values to certain significant figures in Excel. Follow the below steps to do this-
📌 Steps:
- For this, first, go to the top ribbon, press on the Developer, and then press on the Visual Basic option from the menu.
You can use ALT + F11 to open the ‘Microsoft Visual Basic for Applications’ window if you don’t have the Developer tab added.
- Now, a window named “Microsoft Visual Basic for Applications” will appear. Here from the top menu bar, press on the “Insert” And a menu will appear. From them, select the “Module’” option.
- Now, a new “Module” window will appear. And Paste this VBA code into the box.
Function ROUNDSIGNIFICANT(Value As Variant, Digits As Variant)
Dim exp As Double
If IsNumeric(Value) And IsNumeric(Digits) Then
If Digits < 1 Then
' Please Insert the " #NUM " error
ROUNDSIGNIFICANT = CVErr(xlErrNum)
Else
If Value <> 0 Then
exp = Int(Log(Abs(Value)) / Log(10#))
Else
exp = 0
End If
ROUNDSIGNIFICANT = WorksheetFunction.Round(Value, _
Digits - (1 + exp))
End If
Else
' Please Insert the " #N/A " error
ROUNDSIGNIFICANT = CVErr(xlErrNA)
End If
End Function
- After inserting the code in the module, come back to the worksheet. Now, you can use a new function named ROUNDSIGNIFICANT to round values to significant figures in Excel.
- Now, paste this formula into cell E5 and it will return the rounded values.
=ROUNDSIGNIFICANT(D5,3)
- Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column or use Ctrl+C and Ctrl+V to copy and paste.
Read More: How to Count Significant Figures in Excel
Things to Remember
- Using the first formula shown in this article, If you want to change the number of significant figures then you have to change the formula in each cell.
- But using the 2nd formula, to change the number of significant figures, you have to insert the value in the G5 cell.
- While using the VBA code, you don’t have to run the code. Just paste it into the module code and come to the Excel worksheet.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
In this article, you have found how to round to significant figures in Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
<< Go Back to Rounding in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
