How to quickly calculate percentile or quartile ignore zeros in Excel?
When applying the PERCENTILE or QUARTILE functions in Excel, users often encounter situations where their data range contains zero values. By default, these functions include zeros in their calculations, which can significantly affect the results by lowering percentile or quartile values, especially if zero does not represent meaningful data in the context. For more accurate statistical analysis, you may wish to ignore zero values completely when calculating percentile or quartile. This tutorial will show you several practical techniques to solve this problem efficiently in Excel, including native formula approaches, VBA solutions, and appropriate scenario discussions to help you choose the best method for your needs.
PERCENTILE or QUARTILE ignore zeros
PERCENTILE ignore zeros (Array Formula)
To calculate percentile while ignoring zeros, you can use an array formula which evaluates only values greater than zero.
Select a blank cell where you wish to display the result and enter the following formula:
=PERCENTILE(IF(A1:A13>0,A1:A13),0.3)After typing the formula, you must press Ctrl + Shift + Enter (not just Enter), because this is an array formula. Excel will surround the formula with curly braces { }, indicating it’s been entered correctly. In this formula:
- A1:A13 is your data range—adjust this as needed for your own sheet.
- 0.3 specifies the 30th percentile. You can change this value to whichever percentile you wish to calculate (e.g., 0.75 for the 75th percentile).
This method is particularly useful when you want to prevent zeros—such as missing or null measurements—from affecting statistical outputs.
Be aware that pressing only Enter will not work properly; you must use Ctrl + Shift + Enter. Also, formulas with IF(...) inside aggregation functions may be less efficient on large data sets.
QUARTILE ignore zeros (Array Formula)
This approach is similar for quartiles. Select a cell for the result and enter:
=QUARTILE(IF(A1:A18>0,A1:A18),1)After entering the formula, press Ctrl + Shift + Enter to confirm as an array formula.
- A1:A18 is the sample data range (change as needed).
- 1 means you want the first quartile (25th percentile). You can use 2 for the median or 3 for the third quartile (75th percentile).
Make sure your data range does not contain text or error cells, as the formula only works with numeric values. This solution is best for moderate-sized data sets needing a quick calculation without VBA or add-ins.
VBA Macro Filter and Calculate Percentile/Quartile Excluding Zero
You can also use VBA (Visual Basic for Applications) to automate filtering out zero values and then calculating a percentile or quartile on the remaining data. This approach is especially practical when handling large datasets, or when you want to repeat the procedure often without manually entering formulas.
Applicable scenarios: Ideal for advanced users, repetitive tasks, or complex data ranges. By customizing the code, you can handle any percentile or quartile index and any data area.
1. Go to the Developer Tools tab in Excel. If it's not visible, right-click the ribbon, choose Customize the Ribbon, and check Developer. Then click Developer Tools > Visual Basic.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module.
3. Copy and paste the following VBA code into the module:
Sub FilterZeroAndPercentile()
Dim rng As Range
Dim ws As Worksheet
Dim arr As Variant
Dim filteredArr As Variant
Dim i As Long, count As Long
Dim percentileVal As Double
Dim quartileVal As Double
Dim pctl As Double
Dim quartIdx As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the data range (numbers only)", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
' Prompt for percentile value (e.g., 0.75 for 75th percentile)
pctl = Application.InputBox("Enter percentile value between 0 and 1 (e.g., 0.75 for 75th percentile)", xTitleId, "0.75", Type:=1)
' Prompt for quartile index (1, 2, 3, 4)
quartIdx = Application.InputBox("Enter quartile index (e.g., 1 for first quartile)", xTitleId, "1", Type:=1)
arr = rng.Value
count = 0
' Count non-zero numbers
For i = 1 To UBound(arr, 1)
If arr(i, 1) > 0 Then
count = count + 1
End If
Next i
If count = 0 Then
MsgBox "No non-zero data found!", vbExclamation, xTitleId
Exit Sub
End If
ReDim filteredArr(1 To count)
count = 0
For i = 1 To UBound(arr, 1)
If arr(i, 1) > 0 Then
count = count + 1
filteredArr(count) = arr(i, 1)
End If
Next i
' Calculate percentile / quartile
percentileVal = Application.WorksheetFunction.Percentile(filteredArr, pctl)
quartileVal = Application.WorksheetFunction.Quartile(filteredArr, quartIdx)
MsgBox "Percentile (" & pctl & "): " & percentileVal & vbCrLf & _
"Quartile (" & quartIdx & "): " & quartileVal, vbInformation, xTitleId
End Sub4. Click the 
button or press F5 in the VBA window to execute the macro. A prompt will ask you to select your data range (numbers only), then specify your desired percentile (e.g., 0.3 for 30th percentile), and quartile index (such as 1 for the first quartile). The macro will automatically filter out zero values and display the results in a message box.
Pros: Handles large or irregular data quickly, fully excludes zero values, and avoids manual formula input. Allows repeated use and customization.
Cons: Requires enabling macros and some familiarity with VBA. Not suitable for worksheet formulas unless converted to a UDF.
Common issues and troubleshooting: If you select non-numeric or error cells, the macro may skip or display an error. Make sure the data range includes only numbers with zero and positive values. If no non-zero data is found, you will be prompted accordingly.
Tips: You can further customize the VBA code to copy output to a specific worksheet cell, change calculation functions, or automate across multiple ranges. Always save your workbook before running or editing macros to prevent accidental data loss.
If you need to extend this solution to percentile or quartile calculations in multiple columns, consider modifying the macro by looping across columns or ranges.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in