How to Count Significant Figures in Excel (2 Ways)

We can define significant figures as the important figures of a certain number that accurately represent the meaning of that number. The concept of significant figures is very important in the measurement process. By counting the significant figures of various numbers, we can determine how precise or accurate those numbers are. In this article, we will show you how to count significant figures in Excel.


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


Characteristics of Significant Figures

Before going into our procedure, first of all, we will know about the characteristics of the significant figures. These characteristics, or criteria of significant figures, will help us to count these figures and differentiate them from any other numbers. The characteristics are as follows.

  • All digits of a number that are non-zero are significant. For example- 8589 has four significant figures.
  • If there are any zeros placed between two non-zero digits, then they are significant. For example- 8089 has four significant figures.
  • Zeros which are in the leading position of a number, are not significant. For example- 0079 has two significant figures.
  • If the zeros are to the right of the last non-zero digit in a number that has a decimal point, they are significant. For example has 7500. four significant figures.
  • If there is no decimal point, then those zeroes are not significant. For example- 7500 has two significant figures.
  • If the zero digits are to the right of a decimal point in a number, then they are significant. For example- 7500.00 has six significant digits.
  • If the zeroes are at the beginning of a decimal number, then they are not significant. For example- 0.007500 has four significant figures.

2 Easy Ways to Count Significant Figures in Excel

In this article, you will see two different ways to count significant figures in Excel. In our first approach, we will combine various functions of Excel to count significant figures. As for our second method, we will apply a VBA code for the same purpose. To achieve our goal, we will use the following data set.

Easy Ways to Count Significant Figures in Excel

1. Combining Functions to Count Significant Figures

In our first approach to count significant figures, we will combine multiple functions of Excel to create a formula. In the combination, we will use

How we will combine this function and what the outcome will be are given in the following steps.

Step 1:

  • First of all, type the following formula in cell C5.
=IF(B5="","",IF(ABS(B5)=0,0,LEN((ABS(B5)/10^LEN(ABS(B5)))*10^(LEN(ABS(B5)/10^LEN(ABS(B5)))-LEN(INT(ABS(B5)/10^LEN(ABS(B5))))-1)))+LEN(B5)-LEN(ABS(B5))-IF(LEFT(B5,1)="-",1,0)+IF(OR(LEFT(B5,1)=".",LEFT(B5,2)="-."),1,0))+IF(AND(NOT(ISERROR(FIND(".",B5))),ISERROR(FIND(".",ABS(B5)))),-1,0)

Combining Functions to Count Significant Figures in Excel

Formula Breakdown

  • ISERROR(FIND(“.”,ABS(B5))):The FIND function finds the position of the decimal point in cell B5. As there is no decimal point in cell B5, it will show #VALUE! As a result, the ISERROR function sees errors in the result of the FIND function so it will show True.
  • ISERROR(FIND(“.”,B5)): As there is no decimal point in cell B5, it will find an error and display True as a result.
  • NOT(ISERROR(FIND(“.”,B5))): The NOT function will reverse the result found from the previous step. It will display False as a result.
  • AND(NOT(ISERROR(FIND(“.”,B5))),ISERROR(FIND(“.”,ABS(B5)))): As the first part of this function displays False and the second part shows True as a result, this AND function will show False as a result ultimately because both the arguments aren’t true at the same time.
  • IF(AND(NOT(ISERROR(FIND(“.”,B5))),ISERROR(FIND(“.”,ABS(B5)))),-1,0): As the result from the previous step is False, the IF function will show 0 as a result.
  • LEFT(B5,1)=”.”:The first character of the value of cell B5 is not a decimal point. So the result is False.
  • LEFT(B5,2)=”-.”:The second character is not a negative sign or a decimal point. That’s why the result will also be False.
  • OR(LEFT(B5,1)=”.”,LEFT(B5,2)=”-.”): As for the above two arguments, both return False as results, so the OR function will also show False.
  • IF(OR(LEFT(B5,1)=”.”,LEFT(B5,2)=”-.”),1,0)): As the result from the OR function is false, the IF function will display 0 as a result.
  • IF(LEFT(B5,1)=”-“,1,0): The LEFT function checks if the first left character of cell B5 is a negative sign. If it’s not, then it will show False as a result. Then the IF function will show 0 as a result because the statement is not true.
  • LEN(B5)-LEN(ABS(B5)): As both of the character lengths of cell value of B5 are equal so the result will be 0.
  • -LEN(INT(ABS(B5)/10^LEN(ABS(B5)))): The absolute value of cell B5 is 40085 and its character length is 5. After dividing the value by 10^5 it becomes 0.40085. The INT function then returns the nearest integer value, which is 0. The length of this character becomes 1. The minus sign makes it -1.
  • LEN((ABS(B5)/10^LEN(ABS(B5)))*10^(LEN(ABS(B5)/10^LEN(ABS(B5))): The result of the first part of this argument before the multiplication sign is 40085. Then, after multiplication, the result becomes 4008500. So the length of the character is 7.
  • LEN((ABS(B5)/10^LEN(ABS(B5)))*10^(LEN(ABS(B5)/10^LEN(ABS(B5)))-LEN(INT(ABS(B5)/10^LEN(ABS(B5))))-1))): The first part of this argument before the first minus sign gives the result of 7. After adding -2 to it, the result becomes 5. The -2 comes from the -1 that was found after the calculation of the INT function and the -1 that is already present in the formula.
  • IF(ABS(B5)=0,0,LEN((ABS(B5)/10^LEN(ABS(B5)))*10^(LEN(ABS(B5)/10^LEN(ABS(B5)))-LEN(INT(ABS(B5)/10^LEN(ABS(B5))))-1)))+LEN(B5)-LEN(ABS(B5))-IF(LEFT(B5,1)=”-“,1,0)+IF(OR(LEFT(B5,1)=”.”,LEFT(B5,2)=”-.”),1,0))+IF(AND(NOT(ISERROR(FIND(“.”,B5))),ISERROR(FIND(“.”,ABS(B5)))),-1,0): If the absolute value of cell B5 is 0 then it will show 0. Otherwise, it will count the number of significant figures by using the above steps.
  • IF(B5=””,””,IF(ABS(B5)=0,0,LEN((ABS(B5)/10^LEN(ABS(B5)))*10^(LEN(ABS(B5)/10^LEN(ABS(B5)))-LEN(INT(ABS(B5)/10^LEN(ABS(B5))))-1)))+LEN(B5)-LEN(ABS(B5))-IF(LEFT(B5,1)=”-“,1,0)+IF(OR(LEFT(B5,1)=”.”,LEFT(B5,2)=”-.”),1,0))+IF(AND(NOT(ISERROR(FIND(“.”,B5))),ISERROR(FIND(“.”,ABS(B5)))),-1,0): If the cell B5 is blank then the IF function formula will keep it blank. If it has a value in it, the procedure for counting the significant figure will start. We have found two actual values for the formula,  which are 7 and -2. After adding them,the result becomes 5. All the values found from the following IF functions are 0. So, after the final calculation the number of significant figures for the value of cell B5 is 5.

Step 2:

  • Secondly, press Enter to see the significant figure for the value in cell B5.
  • In this case, the significant figure 40058 is 5.

Step 3:

  • Finally, use the AutoFill feature of Excel to count significant figures for the lower cells.


2. Applying VBA to Count Significant Figures in Excel

In our second approach, we will apply a VBA code to count significant figures. We will use a custom function after applying this code. This function will help us to count significant figures based on certain conditions. The detailed steps for this procedure are in the following.

Step 1:

  • Firstly, go to the Developer tab of the ribbon.
  • Then go to the Visual Basic command in the Code group.

Apply VBA to Count Significant Figures in Excel

Step 2:

  • Secondly, you will see the VBA window after selecting the command.
  • Then from the Insert tab choose Module.

Step 3:

  • Thirdly, copy the following VBA code and paste it into the module.
'Defining the function name
Function CountSignificant(Rng As Range, Optional xType As Integer = 1)
'Declaring variables
Dim zCell As Range
Dim yText, yText2 As String
Dim xMax, xMin, xDec, x As Integer
'Set the function as volatile
Application.Volatile
'We will assign the cell values in zcell
Set zCell = Rng.Cells
'Check if the cell value is a number or not
If Not IsNumeric(zCell) Or IsDate(zCell) Then
'if the cell value is not a number then it will show error
CountSignificant = CVErr(xlErrNum)
'After finding error the function will exit
Exit Function
'End of the if function
End If
'if any leading space is found then it will be removed
yText2 = Trim(zCell.Text)
yText = ""
'check the position of the decimal points in cell value
xDec = InStr(yText2, ".")
'we wil remove any characters other than numbers
For x = 1 To Len(yText2)
If Mid(yText2, x, 1) >= "0" And _
Mid(yText2, x, 1) <= "9" Then _
yText = yText & Mid(yText2, x, 1)
Next
'After that we will remove any zero that is in the leading position with a while loop
While Left(yText, 1) = "0"
yText = Mid(yText, 2)
Wend
xMax = Len(yText)
'Then using another while loop, we will remove any trailing zeroes if there is no decimal in the cell value
yText2 = yText
If xDec = 0 Then
While Right(yText2, 1) = "0"
yText2 = Left(yText2, Len(yText2) - 1)
Wend
'End of the if function
End If
xMin = Len(yText2)
'defining two cases; where the first and second case will show minimum and maximum significant digits respectively
Select Case xType
Case 1
CountSignificant = xMin
Case 2
CountSignificant = xMax
'If the condition is not met then it will show error
Case Else
CountSignificant = CVErr(xlErrNum)
'End of the select case
End Select
End Function

Apply VBA to Count Significant Figures in Excel

VBA Breakdown

  • The name of the custom function is CountSignificant.
  • There are three types of variables here; zCell is the range type variable; yText, yText2 are the string type variables, and xMax, xMin and xDec are integer type variables.
  • Set zCell = Rng.Cells: We will assign the cell values of the range in zCell.
  • If Not IsNumeric(zCell) Or IsDate(zCell) Then CountSignificant = CVErr(xlErrNum) Exit Function: This IF function will check if the assigned cell value is a number or not. If the value is not a number or date, then this will cause an error and will end the procedure.
  • yText2 = Trim(zCell.Text) yText = “”: This command will remove any leading space.
  • xDec = InStr(yText2, “.”): This will find out the position of decimal in the numbers
  • For x = 1 To Len(yText2) If Mid(yText2, x, 1) >= “0” And _ Mid(yText2, x, 1) <= “9” Then _ yText = yText & Mid(yText2, x, 1): We will use the combination of the for loop and the IF function to remove any characters other than numbers. Because they are not listed as significant figures.
  • While Left(yText, 1) = “0” yText = Mid(yText, 2) Wend xMax = Len(yText): Using this while….wend loop, we will remove any leading zeroes from the cell value.
  • yText2 = yText If xDec = 0 Then While Right(yText2, 1) = “0” yText2 = Left(yText2, Len(yText2) – 1) Wend: Now, we will remove any trailing zeroes if there is no decimal in the cell value using this while… wend loop.
  • Select Case xType: Then, we will select two case types. In the first case, the CountSignificant function will show the minimum number of significant figures; in the second case, it will show the maximum number of significant figures. Anything other than this condition will show an error.

Step 4:

  • Fourthly, go to your active worksheet.
  • Then, in cell C5 type the following formula after putting the previously mentioned code into the module.
=CountSignificant(B5,1)

Step 5:

  • Fifthly, hit the Enter button and this function will allow you to count the desired significant figure.
  • Then, show the result for the other cells with the help of the AutoFill feature.


Conclusion

That’s the end of this article. We hope you find this article helpful. After reading the above description, you will be able to count significant figures in Excel by using any of the above-described methods. Please share any further queries or recommendations with us in the comments section below. The Exceldemy team is always concerned about your preferences.

Araf

Araf

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo