2011 Excel Mac "Frequency" Function not working

Question

I'm running an older MacBook Pro 17" with OSX 10.6.8 and have a workbook with 8 spreadsheets for consecutive years 2007-2014 that each contain the same data. I've run the "Frequency" function in one spreadsheet and had success with the results. I closed the computer, next day went back to run the function on the other sheets and it produced a result of "0". I went back to the original sheet and for the fun of it deleted the cells containing the function in an attempt to recreate it and had the same negative results on the second round. After several tries in the original sheet to produce the results required it finally worked (no longer producing "0" results) but now it's resulting in inaccurate results.

My entry appears as such; {=FREQUENCY(C6:C209,J11:J18)}. The data array is in column "C", and the bin array in column "J", and I've selected the range of cells in column "K" (K11:K18) when entering the syntax, (I believe that's the proper term). Once the syntax is entered I hold "Shift" & "Ctrl" while pressing the enter key.

Does anyone have an idea of what could be wrong? I've read this function does have issues, but have seen no solutions.

Answer 1

Hi Phillip, but less than 1 minute ago, I found the culprit, the column I had the data in, a series of numbers, all had errors, the little green triangle in the corner. Corrected the cell errors, and eureka, it works!

thank you for taking the time, very much appreciated!

 put all your files including the one you got working in a Folder and upload the folder to one of the following cloud services:

1. OneDrive
2. Google Drive
3. SugarSync
4. DropBox
5. Provide a Link.

I'll see if I can figure it out  send them back to you.

_________

Disclaimer:

The opinions & replies are solely mine, and do not reflect upon my position as a Community Moderator.

Answer 2

put all your files including the one you got working in a Folder and upload the folder to one of the following cloud services:

1. OneDrive
2. Google Drive
3. SugarSync
4. DropBox
5. Provide a Link.

I'll see if I can figure it out  send them back to you.

_________

Disclaimer:

The opinions & replies are solely mine, and do not reflect upon my position as a Community Moderator.

Answer 3

I've run disk first aid with the install disk, verified permissions and disk, repaired both that had minor issues. Now back to square one, the formula is only working on select sheets, this makes not sense to me. If anyone out there has any ideas, would very much like to find a solution.

Thanks

Hi Phillip, as it turns out I didn't have TC backing up and consequently don't have the original file.

That said, I did a "Clear All" in the original sheet and re-entered the frequency formula and now is working, in that sheet only?

Tried the "Clear All" in the other sheets in the range where I need to enter the formula, no go, continues to give me a "0" result.

I'm getting mixed results whether I copy & paste special, values only, formulas only, it works sometimes, other times not.

I'm going to run disk first aid from the OS CD/DVD and see if that doesn't correct some kind of corruption. Will keep you posted.

Here is a possibility. I am sure someone  such as JE, one or the other of the Bob's, or John (all MVP's) will come up with a direct answer. But here is something to Try in the mean time. But will work only if you have been either been: backing up your excel files, or you have been creating Time machine Backups.

1. Locate version of Excel file that was working (by date) If it has been backed up through Excel it will have file name and date beside the file name.
2. Now open that file.
3. Click on the Select All Button

4. Now hold down ⌘ key and type the C key (Copy) 5. Now switch to one of your other Files. 6. Click the Select All Button again. 7. Now choose Paste Special from Edit menu. 8. Choose Formulas. 9. If necessary while the entire work sheet is selected, choose Paste Special again and this time choose Format.

Repeat again with each sheet. Its obvious you have either forgotten exactly how you wrote Formula originally, or you forgot to format your columns/rows the same as the first.

Oh this is a suggestion why 8 different files doing the same thing. Why not one master file and add new sheet tabs  Name them according to Months.

If it something you are going to have to do Yearly and update Monthly I would:

1. Create one file
2. Add 12 Sheets
3. In each sheet I would duplicate Columns and Column formatting in all 12 Sheets.
4. Then don't add any information but save as a template in My Templates.

Now when I needed to create a new yearly series:

• simply go to template and open.
• Save using new title with say the year at the end of the new name
• Click on first sheet and start filling out information.
• Repeat with next sheet until sheet filled up

That way everything is organized in one place you can always make backup incase stuff goes Boom in the night.

_________

Disclaimer:

The opinions & replies are solely mine, and do not reflect upon my position as a Community Moderator.

Answer 4

Hi Phillip, as it turns out I didn't have TC backing up and consequently don't have the original file.

That said, I did a "Clear All" in the original sheet and re-entered the frequency formula and now is working, in that sheet only?

Tried the "Clear All" in the other sheets in the range where I need to enter the formula, no go, continues to give me a "0" result.

I'm getting mixed results whether I copy & paste special, values only, formulas only, it works sometimes, other times not.

I'm going to run disk first aid from the OS CD/DVD and see if that doesn't correct some kind of corruption. Will keep you posted.

Here is a possibility. I am sure someone  such as JE, one or the other of the Bob's, or John (all MVP's) will come up with a direct answer. But here is something to Try in the mean time. But will work only if you have been either been: backing up your excel files, or you have been creating Time machine Backups.

1. Locate version of Excel file that was working (by date) If it has been backed up through Excel it will have file name and date beside the file name.
2. Now open that file.
3. Click on the Select All Button

4. Now hold down ⌘ key and type the C key (Copy) 5. Now switch to one of your other Files. 6. Click the Select All Button again. 7. Now choose Paste Special from Edit menu. 8. Choose Formulas. 9. If necessary while the entire work sheet is selected, choose Paste Special again and this time choose Format.

Repeat again with each sheet. Its obvious you have either forgotten exactly how you wrote Formula originally, or you forgot to format your columns/rows the same as the first.

Oh this is a suggestion why 8 different files doing the same thing. Why not one master file and add new sheet tabs  Name them according to Months.

If it something you are going to have to do Yearly and update Monthly I would:

1. Create one file
2. Add 12 Sheets
3. In each sheet I would duplicate Columns and Column formatting in all 12 Sheets.
4. Then don't add any information but save as a template in My Templates.

Now when I needed to create a new yearly series:

• simply go to template and open.
• Save using new title with say the year at the end of the new name
• Click on first sheet and start filling out information.
• Repeat with next sheet until sheet filled up

That way everything is organized in one place you can always make backup incase stuff goes Boom in the night.

_________

Disclaimer:

The opinions & replies are solely mine, and do not reflect upon my position as a Community Moderator.

Answer 5

Here is a possibility. I am sure someone  such as JE, one or the other of the Bob's, or John (all MVP's) will come up with a direct answer. But here is something to Try in the mean time. But will work only if you have been either been: backing up your excel files, or you have been creating Time machine Backups.

1. Locate version of Excel file that was working (by date) If it has been backed up through Excel it will have file name and date beside the file name.
2. Now open that file.
3. Click on the Select All Button

4. Now hold down ⌘ key and type the C key (Copy) 5. Now switch to one of your other Files. 6. Click the Select All Button again. 7. Now choose Paste Special from Edit menu. 8. Choose Formulas. 9. If necessary while the entire work sheet is selected, choose Paste Special again and this time choose Format.

Repeat again with each sheet. Its obvious you have either forgotten exactly how you wrote Formula originally, or you forgot to format your columns/rows the same as the first.

Oh this is a suggestion why 8 different files doing the same thing. Why not one master file and add new sheet tabs  Name them according to Months.

If it something you are going to have to do Yearly and update Monthly I would:

1. Create one file
2. Add 12 Sheets
3. In each sheet I would duplicate Columns and Column formatting in all 12 Sheets.
4. Then don't add any information but save as a template in My Templates.

Now when I needed to create a new yearly series:

• simply go to template and open.
• Save using new title with say the year at the end of the new name
• Click on first sheet and start filling out information.
• Repeat with next sheet until sheet filled up

That way everything is organized in one place you can always make backup incase stuff goes Boom in the night.

_________

Disclaimer:

The opinions & replies are solely mine, and do not reflect upon my position as a Community Moderator.