'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel FREQUENCY Function Not Working on Mac or PC
Question details
The user needs to troubleshoot the Excel FREQUENCY function, which fails to return the correct array results when entered with data and bin arrays using Control+Shift+Enter, particularly on Mac or iPhone.
- Product
- Microsoft Excel
- Device & OS
- Mac and iOS
- Scenario
- Calculating statistical frequency distributions using the FREQUENCY array formula.
- Observed behavior
- The formula fails to calculate properly or returns errors despite using the correct Control+Shift+Enter keyboard shortcut for array formulas.
Verify that your bins array is sorted in ascending order and that you have selected one more cell for your output range than the number of bins you specified.
Isolate the Issue with a New Blank Workbook
Creating a new file helps determine whether the problem is caused by a corrupted workbook or an issue with the Excel application itself.
Often, hidden formatting issues or workbook corruption can prevent array formulas from calculating correctly. Testing in a clean environment isolates the root cause.
Open Excel and create a completely blank workbook.
Enter a small sample data array in column A and a sample bins array in column B.
Highlight the output range, type =FREQUENCY(A2:A10, B2:B5), and press Control+Shift+Enter (or Command+Return on Mac).
If the formula works perfectly in the new workbook, the original file is likely corrupted. Copy your working data over to the new file.
Verify Office and macOS Version Compatibility
Check your application versions to ensure array formulas are fully supported and behaving consistently across devices.
Re-enter the Array Formula Correctly
Legacy versions of Excel strictly require array formulas to be finalized with a specific keystroke combination rather than just the Enter key.
Calculate Frequency Distributions Seamlessly in WPS Spreadsheets
WPS Spreadsheets provides robust support for complex data analysis, including full compatibility with the FREQUENCY function and array formulas. Easily calculate data distributions across desktop and mobile devices without worrying about version conflicts.
- 1. Open your dataset: Launch WPS Spreadsheets and open your .xlsx workbook containing the raw data and bins array.
- 2. Select the output range: Highlight the blank cells adjacent to your bins array where you want the calculated frequencies to display.
- 3. Type the formula: Enter =FREQUENCY(data_array, bins_array) into the formula bar.
- 4. Execute as an array: Press Ctrl+Shift+Enter (or Cmd+Return on Mac) to calculate the distribution array instantly.
Frequently Asked Questions
Why does my FREQUENCY formula show a #VALUE! error?
This error often occurs if you did not press Control+Shift+Enter when completing the formula in older versions, or if your data array contains non-numeric text values.
Why is there an extra zero or number at the end of my FREQUENCY results?
The FREQUENCY function always returns an array that is one item larger than your bins array. This extra item represents the count of any values that are greater than your highest specified bin.
Can I edit or delete a single cell within the FREQUENCY array result?
No, you cannot change or delete part of an array. To edit the formula, you must select the entire output range of cells containing the array, make your changes in the formula bar, and press Control+Shift+Enter again. To delete it, select the entire range and press Delete.
Does the FREQUENCY function work on mobile versions like Excel for iPhone?
While the mobile app supports viewing formulas, entering complex multi-cell array formulas via the mobile touch interface can be difficult. It is highly recommended to set up the array function on a desktop version first.
