Numbers: unwanted interpretation of the formatted display instead of actual value

Thanks for your work Badunit.

Following that I made another attempt.

I'm going to avoid to use the word "date" because there are no dates at all anywhere in this whole table nor in the discussion.

Here is what I set up:

Column A is just the numbers, no custom format.

Column B is the numbers that I want to add up with SUMIF

Column C is the same as A, i.e. numbers, but displayed with the conditional format that puts the hyphens in:

As you see, the type is Number.

Column D has values that will be used in the conditions of the SUMIFS. Two are not formatted (D10, D11) and two are formatted (D17, D18).

Column E has several SUMIFS, all of which should give the same result.

For clarity, I have also pasted their formulae as text in the adjacent cells in column F.

First, E13 shows 0 (zero), it gives no error but the result is completely wrong, as you also pointed out. Very dangerous!

E14 works, because the condition is now made up differently, but I fail to see why it should be different.

E15 uses the column with the formatted numbers and is still giving the right result (as you also pointed out), but it still uses the condition values like in E14, i.e. they are built-in numbers.

E17 goes wrong but does at least tell me it does. It uses the condition values from the formatted cells.

E18 is correct, but it uses the condition values from the unformatted cells D10, D11.

It seems therefore that the problem is narrowed down to the way SUMIFS interprets the cells referred to in the conditions. It takes the formatted text, not the value. Therefore it works if the cell displays a character string that looks like a number but not if it contains the hyphens. (there must be a lot of guessing going on behind the scenes for it to come up with the report that I'm trying to compare with a date!)

Of course I do not care about what Excel does. The majority is not always right, and compatibility is also sometimes a way to stagnate.

(rhetoric questions:)

First, it is somewhat strange but understandable that the condition must be a string, however what is the difference between F13 and F14?

Second, in F15 and F18, why does it take the values of the data in C but not the values of the cells D17, D18 ?

What is needed is a function VALUE(cell reference) which explicitly ignores the formatting.

Sometimes formatting is needed, e.g the function LEFT(cell,n) must work on the character string of the display, not the value.

I can of course work around the problem by using a complicated string function to convert D17's display string back into a string without the hyphens. But given the number of sheets and tables I have (hundreds) that use this custom format and do SUMIFS with conditions taken from other (often computed) cells, this is not an option.

For the time being I'll stick to Numbers 2.3, which is also much more comfortable to use on large spreadsheets, and in parallel continue to work on a JavaScript/php/HTML alternative that will do a better job of separating value from format. (one of the things I'm putting in that alternative is the possibility to write a single formula for a whole column, so that I don't need to propagate when a change is made)

Thanks for your efforts, they helped narrow down the problem.

Interesting.

Maybe we should all work on the same sheet.

I've put mine here:

http://www.cailliau.org/sumifs.numbers.zip

and added some stuff:

D16 is entirely correct but not viable to do: too many places to edit, and really ugly.

It's at the moment that SUMIFS retrieves the cells to build the conditions that it uses the display instead of the value. If the problem were with the A column, I would create a second, hidden column, with the same values but not formatted. Unfortunately that's not where the problem is.

I looked at VALUE again, but it does almost exactly the opposite of what it suggests. As per the example, if I put €100.49 into a cell and put VALUE of that cell elsewhere, it coes up with 100. Throwing away almost half a euro. If VALUE took the formatted cell really as a date and converted it to the unix date number, then it would be usable, as in: 2021-08-09 = 1628493007

But it does not. For Apple it would be the quick fix and not even a kludge.

Badunit, that is interesting. So something has changed in the VALUE function. Sigh.

Can you send a screenshot of the explanations of the Value function in your Numbers version?

Yes, and I had not thought of using SUBSTITUTE which indeed is a lot simpler! Thanks.

I do have a brand new machine running Catalina (long and very bad story), I'll have a look there, but it will take some time.

Post Tenebras Lux! (*)

That is significantly different from the nonsense explanations in the other versions, including 4.3.

It seems to do it right even if no mention is made of "dates".

So that may be the solution. Thanks.

And :-) you get a bonus rant from me: I typed in $123 and it returned an error (VALUE requires a string specifying a number, but found “$123” instead.)

But €123 worked.

Now, this is one of my favourite rants: most applications and websites assume the user is a monolingual person living in the middle of a uniform country. I and most of my colleagues don't fit that at all. Worse: my spreadsheets use all kinds of currencies, though mostly it's euros and Swiss francs. I typed 123EUR which gave an error, but 123CHF was accepted; 123USD was again rejected. I have no clue why CHF was OK. There is nothing in my system preferences that indicates Switzerland!

My wife is from the UK, we live in France, but only 2km from the Geneva border and the closest shopping centre is 4km away, in Switzerland. So we always have EUR and CHF in our wallets (try to find a wallet with two pouches for coins, they do exist but are very rare) and in our accounts GBP frequently turn up. Before the euro the situation was chaotic, because my family lives in Belgium, a stone throw from the Dutch and German borders. Nevertheless, our home language is English, and it is very annoying that websites always show us the French version, sometimes not even allowing you to switch.

I had to order my laptops specially, otherwise I would have ended up with a Swiss-French keyboard. I need []{} (though I also need éèêà).

There was a time that Apple thought the System Preference's Region panel was unnecessarily complicated and only allowed you to choose a country. You got that country's "package". Fortunately I was able to copy the previous OS version's panel and keep porting it until Apple reinstated the full works. I need the system in English, but metric, euros as currency, ºCelsius, Monday as the first day of the week, 24h clock, decimal point (not comma), grouping by apostrophe (not comma or dot), etc. There are still applications and webstites where I cannot get 24h clock if I choose English.

I have to physically go to the UK or US to try out an external ergonomic keyboard with the right engravings.

I am a citizen of Belgium, we will go there on holidays coming this Friday, but I have a COVID vaccination certificate from my hospital in Switzerland. Fortunately that is now accepted in the EU, but only since a month or so. Most people have no idea how silly situations can get when one is (has been) an international civil servant.

And Numbers does not seem to allow one to have more than one currency.

(end of rant) :-)

(*) the motto of the Republic and Canton of Geneva, but also used in wider senses

:-)

Yes, Paul: "and inconsistent and disappointing result".

The type is indeed greyed out.

We need to re-think spreadsheets. I would dearly prefer the use of strict typing and the four characteristics I mentioned earlier.

The introduction of separate tables in a sheet was a big step forwards, and the (partial) factoring out of formulae from a table was also a great step (Improv, by Lotus, on the NeXT, though I admit immediately that I don't remember exactly how that worked, I remember only the "toilet roll").

Numbers took only the first step. But we need much more. I'm too old to start writing a true native application now, though I'm tinkering with an html/JavaScript/php version. (you can join if you like... ;-)

"The step at the bottom of the stairs."

When I studied computing in 1971, one professor told us the user should never thus be surprised. He compared it to going down flight after flight of stairs, carrying something so you could not see the steps. Then, if for some reason the last flight has an unexpected extra step, you fall over.

The currency table is like that. It probably comes from a desire not to upset traditions, not teach the user anything, but just keep perpetuating the outdated habits. And again of course does not at all take into account people who live internationally. There are more and more of us though.

The table could easily include all the codes.

Sigh.

Thanks Paul. Another tidbit of weird information I have to remember.

Sorry, small mistake in the text of the formula in F14, it must of course sum column B not C, and look at column A not B:

SUMIFS(B,A,"<20210820",A,">20210810)")

(typo result of simplifying)

I was not going to rethink it here, no worries.

Anyway, thanks all of you, it was a pleasure talking to you.

I know what I'll do now.

Have fun,

Robert.