Need to show a zero instead of a blank field in Numbers

If you want all the blank cells to BE zeros, not be blank cells that look like zeros, here is an idea that requires no formulas or extra columns if you have less than 250 numbers:

1. Have at least one zero in the column. Put one in if you have to.
2. Select the entire column of numbers and blanks
3. Change the format to pop up menu.
4. Move the 0 to the top of the list. (If you don't have a 0, create a new item and type in a 0.)
5. Choose "Start with the first item"
6. Change the format to Automatic (or your desired numeric format).

A short Applescript can do the job, as Barry said:

tell application "Numbers" to tell front document
	tell active sheet
		tell (first table whose class of selection range is range)
			set value of every cell in the selection range whose value is missing value to 0
		end tell
	end tell
end tell

1. Open the Script Editor app.
2. Paste the script into a new document.
3. Go to Numbers and select all the columns that have the numbers and blank cells where you want the blank cells to be zeros.
4. Go back to Script Editor and run the script (press the Play button)

If this works for you, you have a few choices of how to save and use it. Here are two.

• You can save it as an app and then run it as an app. You could put it as an icon in your dock for easy access.
• You can save it as a script in the Script folder in your user Library folder and run it from the menu bar. To turn on the Scripts menu, go to the Script Editor app and bing up its preferences. One is to show the menu on the menu bar. When it is selected, a fancy scroll will appear in the upper right side of the menu bar. Click it to see scripts.

Using a three column imported data table for the example.

Make a 'permanent pair' of tables to accept the data and do the calculations, and a third table to be the 'permanent' record and display table.

Paste the incoming data into the first table.

Select all result cells in the second table, and copy.

Select the first (top left) data cell in the third table. Go to the Edit menu and choose Paste Formula Results.

Cleanup:

Select all data cells on the first table. Press delete.

You'll now have an empty first table, second table filled with zeros, both ready for use with the next day's data.

And a third table containing today's data, with zeroes where blanks were imported. This may be kept, or may be cleared and used for the next day's data if you are not preserving the table.

If you are keeping the filled table(s), Duplicate the now empty First table, and use the Duplicate as the third table for the next day. (the formulas in the second table reference the contents of the original copy of the first table).

Copy the results from the table containing the formulas.

Click on the top left data cell of your 'final results' table, then go to the Edit menu and choose Paste Formula Results.

Paste Formula Results strips the formulas and pastes the most recent results calculated by the formula, giving you a stable set of values in that table.

Regards,

Barry

They're 'basically the same' because it's the only method either of us are aware of. The only difference between them is the extra step of moving the converted values into a new table as fixed, rather than formula dependent, values.

For most errors, IFERROR can be used as an error trap. to provide a default response if the enclosed formula is returning an error. It doesn't work for self-reference errors where it is the IFERROR function referencing its own cell.

An alternate track might be had in an AppleScript, saved as a Service, written to get the value in all cells of the table and replace the blanks with the value zero. As Service, it would be installed as a menu item in the Numbers > Services, and could be assigned a keystroke combination to activate it. Beyond my ken, but might be of interest to one of the scripters participating in this community.

Regards,

Barry

You misunderstood my comment, which was my fault. I had made a long post about your spreadsheet and a possible solution but it was for a spreadsheet you were creating/editing every day, not getting from someone else. It was a total misreading of your problem. I assumed you got an email with that long post before I deleted it and replaced it with the comment about MY poor reading comprehension and how I missed the word "get" in your post.

I tried this on my Numbers Spreadsheet. What I did was type "0.00" into a field and click SAVE and it was saved. I also tried "0" and that worked too. Generally, entering zero could be considered "redundant" data but you require it so perhaps there is a setting you need to change to allow what the computer considers unnecessary data but this expertise is beyond me. 0 is arithmetically nought but uses data to be graphically represented and many thousands of cells filled with zeros could add up to a memory issue?

reading comprehension... "I get a spreadsheet"

I should have said that I typed in a cell, not a field. I'm not a techie as you can tell.