The Importance of Actual Values In Numbers

Since cells in Numbers are formatted in different ways, it is easy to have two cells appear to be the same, such as a date and the text name of a month. When using formulas to compare cells, the functions will often not match up these values because they are fundamentally different. You can use the bottom bar in Numbers to view the actual values of cells to see which value is actually stored in the cell.

You can also watch this video at YouTube (but with ads).

Video Transcript: Here's something you may run into if you use Numbers a lot. In this table here I've got just a list that happens to be a list of months. In this table here I've each month and I'm using a simple formula here, COUNTIF, and it's counting from this column here the months and it's taking the value from the left here. So its going to count the number of January's here. The result if zero. Zero for February and zero for March. Why is this? You can see January and January. They match perfectly. It should find four of them. It should find three February's and two March's. But it's finding none. What's the problem here?
Well, this is something that actually I've gotten people asking me about three times in the last month and it's happened before. So I thought I'd talk about it. 
There's a different between what you see in a cell and the actual value that's in there. Now sometimes it's exactly the same. But in some cases, and in particular with dates, it can be different. So when the word January was typed here in the cell it was instantly converted to a date format. Actually a time format. It was taken as the first day of that month in that year. So in this case these months were typed last year, in 2017. When the month of January was typed the actual value put in there was January 1, 2017. 
Now how do I know this? Well, if you look at the bottom left of the Numbers window you get a little box here and it says Actual. Telling you the actual value is, and it puts it over here. So while you see the formatted value in the table, you see the actual value down here. So the actual value isn't January, that word. But the date 1/1/2017. You can see it's the same for all of these. For February it's 2/1/2017.  That's the value that goes in there. 
Now what happened over here is this table was created later. So if I click here for January you can see that this was the same thing. Typed the word January. So the same data was entered for each of these. But since it was 2018 the value entered there was 1/1/2018.
So when you're doing the COUNTIF formula it's comparing the value of this cell with the values here. So it's comparing January 1, 2018 and it's only finding January 1, 2017. So it doesn't find any 2018 and the result is zero. Now notice when I select this formula here and I can just select it here I don't have to double click it to enter the formula. If I select the formula here instead of Actual in the bottom left corner it says Formula and it gives me the formula. So this area is staying true to itself and basically showing me exactly what's in the cell. So the table is actually showing me the result, in this case zero. 
But at the bottom here I can see this is actually a formula and here's the formula. I can't edit the formula down here. I have to do it in a cell. But at least I can view it. So I can view this cell and see the actual value is 1/1/2017. I can view this cell and see it's 1/1/2018. And I can view this cell to see it's a formula that's COUNTIF, month, and then a 1.
Now how do I correct this problem. Clearly if you're trying to just enter in the names of months here and you don't really care about an actual date, like January 1, 2018, then what you need to do is you need to convert the format from date to text. Now you can do it in either area here. So let's do it over here. I've got January 1, 2018, February 1 and March 1. Let me select this whole column here. I'll go to the right sidebar here to Format, Cell and change the Data Format to Text. What will happen here is now when I select this it says that the format here is actually January and it's a piece of text. It's not an actual date. 
Let's do the same thing for here. I'll select all of these and I'll change the format to Text. It changes all of these to actual pieces of text now. Now you can see they match. The actual piece of text for January, the word January, matches these four January's here and you get the numbers 4, 3, and 2 to match perfectly.
So it's important when you're doing formulas, when you're doing things especially with month names and such, to actually pay attention to the format. Pay attention to the actual value here in the bottom left corner of the Numbers window.

Comments: 4 Responses to “The Importance of Actual Values In Numbers”

Lali Raj

7 years ago

Is there a way to edit the value in formula bar similar to excel?

Gary Rosenzweig

Lali: No. You edit right in the cell.

Nick

hey Gary
I'm trying to wean myself out of Excel more into Numbers, but having a heck of a time getting the date format right. I've formatted cells in Numbers as a date with the display format of May 5, 2018 and I know that the actual number in the cell is displayed as 2018-05-05. However when I use the dates as the X axis in a chart, I can't seem to be able to enter the dates in the Min and Max boxes, it automatically enters the hours minutes and am or pm even though I specified none for time,

Nick: What are you trying to do, exactly? Using dates and min/max doesn't quite make sense. Min and max values are for axes that have numerical values.

Comments Closed.

The Importance of Actual Values In Numbers

Comments: 4 Responses to “The Importance of Actual Values In Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information