Excel custom formatting numbers (0 especially)-CodePudding

Based on

Question 1: What's the explanation of the incorrect sum result in B6 versus the correct one in B7?

Question 2: Is there a different way to display a zero as blank, but keep the ability to calculate the range containing that value?

CodePudding user response：

Question 1:

The TEXT function returns text and computers can't add text strings together. They can only add numbers together.

Excel will sometimes automatically perform type conversions to allow formulas to deliver what Excel guesses as being the expected result. In this case, it is sometimes turning text into numbers before adding them together. This can make like easier for users but the inconsistency can easily lead to errors.

Say A1 and A2 are formatted as text and contain the text character "1". Excel will do an implicit type conversion for operators ( -*/):

=A1 A2

It won't do conversion for ranges in functions:

=SUM(A1:A2)
=SUM(A1,A2)

When taking in a range, the SUM function will ignore all text, so that it can still sum the numbers in the range without throwing an error. If all cells in the range contain text it will return 0.

However if you tried to use the addition operator on two cells containing text that can't be converted to numbers, it will throw an error.

Note that when you put "A1 A2" inside the SUM function, excel first evaluates the addition operation (as this is a single input within the function which must be evaluated first), so it converts A1 and A2 to numbers at this point to create a single numeric result, and then the SUM function takes just the single numeric value as input and returns it again as the total.

If you use SUM with two separate inputs, as =SUM(A1,A2), it doesn't convert either input to a number first.

Question 2:

To get the correct result using the SUM function over the range, you can modify the original sequence formula so that it is delivering numeric values. This can be done in various ways:

1 - Convert the text back to a number by multiplying by 1 (forcing another implicit type conversion), handling the error generated for the nullstring which can't be converted to a number:

=IFERROR(TEXT(SEQUENCE(4,,-2),"0;-0;")*1,"")

2 - Test for 0 using an IF statement and return the null string:

=IF(SEQUENCE(4,,-2)<>0,SEQUENCE(4,,-2),"")

3 - Invert the sequence twice, which throws an error only when it is equal to 0:

=IFERROR(1/(1/SEQUENCE(4,,-2)),"")

OR you can modify the SUM formula to convert the range to numbers on input:

=SUM(IFERROR(B1#*1,0))

However this approach requires you to modify all formulas that look at the original sequence. If the original sequence is intended to be used as numbers (as it is in this case), it is better to have it be generated as numbers in the first place.