Excel Table with formula not calculating

Question

I have an Excel spreadsheet turned into table.

I have a column that's populated with a formula, but when I entered data the formula is not calculating.

As you can see, column [AgeGroup] does not calculate for column [Age] in the second row.

Answer 1

have to turn on calculation options -> automatic

enter image description here

Answer 2

Interestingly, there is another possibility with variants, though the solution here was certainly a nice and quick one!

The image given allows the following to be a possibility, though unshown material to the left (columns A and B) could create the same image and so the issue would NOT be due to the following.

If one has a Table which has more than the beginning row, one may have formulas in a column which need information from, say, blank cells in their row. And if, like in this case, a blank cell SHOULD generate a blank from the formula present, then it certainly would.

Further, if one deleted all the data under that first row, one might delete the formula as well. And if not re-sizing the Table but rather beginning to enter data in the now-empty rows, one might see no output from... no formula.

One might then add a row or rows and notice the formula appears in those rows' column for it. So one sees the problem when adding, say, row 2 here, goes to the end of the Table and adds a new row by entering column A or B data, and sees a happy result: the formula is present in that cell, though in this case, it is properly showing a blank.

That's a house of coincidence cards, but with only (up to) four rows under the last clear successful formula above the selected cell, it IS possible.

Solution then, of course, would be to resize the Table by deleting the rows one wiped of data, or via the control in the lowest, rightmost cell's corner, clearing anything that persisted in the not-in-the-Table-any-longer cells and continuing.

When testing, I came across an odd thing. Two really, but related closely. Outside of a Table's confines, if one enters a number in a General formatted cell it is displayed as a number, right-justified in the cell, taken as a number by all functions, and so on. If one then reformats to Text format, many times it will persist in that display mode and formulas will still treat it as a number though some will not.

And if a formula was present instead of a number, its result will usually still be used, even by a formula created AFTER the reformatting(!). One can "coerce" the cell into the desired situation by pressing F2 and Enter, and in other ways, and then the reformatting will "take" in all respects.

But in a Table, reformatting a formula's cells to Text and using some basic coercion techniques, the formula keeps working although any numeric results will be presented like Text, left-justified and so on. The formulas KEEP ON WORKING, changing results for a newly entered value the formula uses!

Not sure what use that could have, or if it's a bug that will be fixed in some random future version but... interesting.

Answer 3

It looks like you're referencing the cell values incorrectly. The way you have it written, [Age] references the entire field. You want [@Age] to reference the value in that field and in the same row as the formula. See: Using structured references with Excel tables - Microsoft Support

=IF(ISBLANK([@Age]),"",IF([@Age]<18 ... et cetera

As an aside, you can be more efficient with the IFS() function which is like multiple IF() statements but without all the nesting. It takes multiple condition,result pairs and returns the result that corresponds to the first true condition. You can make it even more efficient by reversing the order of your numerical checks but you should keep the blank check at the beginning. In the example below, I converted it to a NOT(ISNUMBER()) check to catch those cases where someone inputs text instead of a number.

=IFS(NOT(ISNUMBER([@Age])),"",[@Age]>=85,"85+",[@Age]>=65,"65 to 84",[@Age]>=40,"40 to 64",[@Age]>=18,"18 to 39",[@Age]<18,"<18")