Sum Function Returns Zero But Works Properly Inside a Formula?

So I understand that if the SUM function is used to total a range, and the data is formatted as text, it will return zero.

But what I do not understand is why SUM works if used in a formula to total the results of other formulas that are returned as text.


In the attached sheet I have the following text string in cell B3: PRX 19 1 - 2 - 1 $53,940

Just to be clear, it is a record for a horse that raced at PARX racetrack. The horse raced 19 times, finished 1st once, second twice and third once. The total purse money won was $53,940.

So I wrote this formula in cell B5 to calculate the percentage to show out of his 19 races, how often the horse was able to finish 1st, 2nd or 3rd.

Formula:

Please Login or Register  to view this content.

The formula works perfectly and the result = 0.210 or 21%

But if the results of those TEXTAFTER(TEXTBEFORE formulas are text, not numbers, I don't understand why this formula doesn't return an error.

So I dissected the formula above. Each section of the formula is now like this:

F3 = TEXTAFTER(TEXTBEFORE(B3," ",3)," ",2) which returns 1
F4 =TEXTAFTER(TEXTBEFORE(B3," - ",2)," - ",1) which returns 2
F5 = TEXTAFTER(TEXTBEFORE(B3," $")," - ",2) which return 1

Now in F8 the SUM function is used to calculate the total of the cells in the column F, Just like it summed the results of those same exact formulas in B5, and it returns zero!

So I'm confused. Why does the formula in B5 return the correct result when it seems to be doing the same thing, summing text, not numbers?

I think it's because you are dividing the SUM of text values by a text value. The mathematical function coerces Excel into treating the text values as numeric values.

Try:

Formula:

Please Login or Register  to view this content.

@TMS I thought that might be the reason, also.

But as shown in the formula below, if you remove the divide section at the end of the formula in B5, so that it only sums the results that are returned by the three TEXTAFTER(TEXTBEFORE formulas, it returns the correct result, 4.

Formula:

Please Login or Register  to view this content.

So that tells me that the dividing the SUM of text values by a text value is not what is causing Excel to treat the text values as numeric values.

Also, to be clear I am not really looking for a solution as the formula in B5 is returning the correct result. I am looking to understand why Excel doesn't return an error or incorrectly calculate the result. Because I know SUM will not properly calculate the total of numbers formatted as text. Yet it seems to be doing just that in my formula. Hopefully someone can shed some light on this.

If A1:A3 contains the number "1","2","3" as Text, then SUM(A1:A3) will =0
However, if you sum each discrete cell as the following value: SUM("1","2","3") will = 6
The formula you are using is also trying to separate 3 string numbers, then sum them together:
=SUM("1","2","1")=4
Since I don't have Ex365, I'll try testing with the MID function
F3="PRX 19 1 - 2 - 1 $53,940", with numbers 1,2 and 1 at positions 8,12 and 16 respectively
I will use:
=SUM(MID(B3,8,1),MID(B3,12,1),MID(B3,16,1))=4
(see attached file)

Just add -- to the start of each of the formulae in F3, F4 and F5 to coerce the text it returns into a number, e.g.

=--TEXTAFTER(TEXTBEFORE(B3," ",3)," ",2)

I think it's what one would refer to as one of those undocumented features that sometimes creep into software.

On the face of it, it should not work, as =SUM(F3,F4,F5) does not.

If I were you, I'd not lose any sleep over it. You know, and that's all that matters.

Or go and ask Microsoft.

PS The common denominator would seem to be that you are using 365 functions if it doesn't coerce automatically with MID - 365 must handle native functions differently to legacy ones.

For what it's worth, as well as using the double negative, you can add or subtract zero, or you can multiply or divide by 1.

I am curious, too, but I know when it's time to stop!

I misread the point about MID - so if it DOES coerce correctly, then it must just be to do with the formula being coerced inside another formula. Although, having said this, there will be occasions when it doesn't.

I think the key thing here is knowing the hack and employing it when coercion does not happen automatically - by the way, I was quite justified in sharing the hack as there was no indication in your opening post (unless I've misread it) that you knew it.

@TMS Thank you. Yes, I am aware of the various ways to coerce Excel into returning text into a numeric value. However none of those are being used in my formula, yet it still works.

Also, I replied in post#3 to your earlier post to show how the divide function in my formula is not the reason it works.

So far nobody has been able to explain why the SUM function returns the SUM of text values and gives the correct result without using the double negative, adding or subtract zero, multiplying or dividing by 1.

Like Ali said, perhaps it's just one of those undocumented features that sometimes creep into software.

And like Ali said, perhaps it's time to stop and just let it go.

Thank you to everyone who commented and tried to make sense out of this.

I love this forum, everyone is so helpful.

Yep - call it a day.