Sometimes, cell formatting shows (!) as number whilst it persistently remains (!) text

Post Reply
sft10
Posts: 5
Joined: Fri Jun 26, 2020 2:31 pm

Sometimes, cell formatting shows (!) as number whilst it persistently remains (!) text

Post by sft10 » Mon Jun 29, 2020 4:08 pm

I begin with a copy of my very last paragraph, I seem to have discovered a (rare) bug in PM:

So, the question is, why was my try to change the cell format in PlanMaker unsuccessful, albeit the control-1 dialog in PM displaying the cell format change to be successful (as said, to "number, 2 decimals)? Cell reformat to number WILL be successful in Excel, for the same cell, I tried over there, the addition result was correct in Excel, and when I then reopened the worksheet in PM, it was recognized correctly in PM, too, but only after the reformatting of the cell in Excel, whilst all the reformatting of the cell in PM was, albeit clearly indicated as having taken place by the dialog, not successful in fact.

Here's my post from start on, with the whole way of my tries. It's obvious it's NOT a formula problem, as I had initially thought, but the problem is that some cells are so "deeply" text-formatted that reformatting them (by Format - Cell - Number - Default or even Format - Cell - Number - Number) in PM will NOT work in reality, even though after the "change", the PM dialog will display the "wanted format", whilst the real format remains "text", but that's then only obvious by calculation "errors" caused by the not taking account of the values of those real-text fields of which the dialog pretends they're formatted as number.

(I needed Excel to resolve the problem, there was no solution in PM.)

So here's what I did:

Sometimes, even a very simple formula, like, in "AJ50", =SUM(AJ48:AJ49) does NOT work correctly, i.e. by displaying the sum of both fields above, but just shows the content of the last cell to be included within the sum of the sum field, i.e. if AJ48 contains 10,15 and AJ49 2,05, then AJ50 should contain 12,20, but it contains 2,05 instead.

This occurred after copying and inserting (^c, ^v) the same formula from field AK50 which is =SUMK48:AJ49 and which works correctly.

So I tried to copy the whole bunch (AJ48 to AK50) to another place; the formulas were correctly transposed to that other location(' field denominators), but the behavior of both formulas (AJ wrong, AK correct) remained the same in the new location.

So I deleted the wrong formula and entered it manually, anew, in the entering line: Same result. I also did this by pressing F2 and then deleting and rewriting from scratch the AJ formula in the special renaming dialog, with again the same result.

I also selected the number fields concerned and optioned for "number format: default" which also didn't change anything. Also, I replaced the numbers by other numbers. (No "number as text" or similar, no leading apostrophe, leading "minus" symbol or anything, just 2-to-3-digit numbers with 2 digits after the comma, and I did not make any error in entering the correct field names, i.e. didn't "leave out" inadvertently the field which is not included into the sum.)

When I manually enter all the needed numbers and formulas in another location of the worksheet, everything works as expected.

I remember I had encountered this problem in another worksheet before. Saving the worksheet, then reopening it will NOT change anything:

The problem with this kind of problem is that upon check, the formula is correctly displayed, while not working correctly in such instances, which becomes obvious for additions with just 2 elements, but would probably remain unnoticed for additions with multiple elements - I do NOT know if it's then systematically just the very last element of the addition which is then retrieved (which may become obvious of course), of if it may also leave out other elements within the addition.

So I made another try: I added a third element on above the numbers to be added by the formula, and adjusted the formula accordingly, to =SUM(AJ47:AJ49). Now the formula (in field AJ50) correctly added the new number (in field AJ47) and the very last one (in field AJ49), but always left out the number in field AJ48.

So I selected that faulty "cell" and looked up its format, it was, as expected, "Number format: default" (i.e. NOT "text" or other), and the number in it (218,26 in this case) was obviously not recognized as a number by the formula.

So I changed the field format (always in the control-1 dialog) to "Number, decimal places 2", and this did not make any difference either, not even after saving, closing and reopening the worksheet: by checking with control-1, the cell format of the number which is NOT included in the addition, is clearly "Number, decimal places 2" now, and though, the formula leaves out the value in that cell - which btw had never been formatted as "text" before.

But the worksheet, many (!) iterations before, was an Excel one...

Finally, I saved the worksheet in the native PlanMaker format, and after reopening checked all these again: The problem subsists, while according to control-1, the cell containing the number not being taken into account, is clearly formatted as "number with 2 decimals".

So it seems that "some cells" just refuse to be processed by formulas (or by the addition formula, in this case), without the user having a chance to become aware of this except by chance, and even when the user copies them, to another place, and changes the number in such a cell, it always refuses to be included into the processing of the formula.

I finally reopened the .xlsx (from PlanMaker) worksheet in Excel (2016); here, I immediately got that green triangle in the cell, and the dialog told me, "Number stored as text", and the sum in the formula cell did not that value either.

So, the question is, why was my try to change the cell format in PlanMaker unsuccessful, albeit the control-1 dialog in PM displaying the cell format change to be successful (as said, to "number, 2 decimals)? Cell reformat to number WILL be successful in Excel, for the same cell, I tried over there, the addition result was correct in Excel, and when I then reopened the worksheet in PM, it was recognized correctly in PM, too, but only after the reformatting of the cell in Excel, whilst all the reformatting of the cell in PM was, albeit clearly indicated as having taken place by the dialog, not successful in fact.

sft10
Posts: 5
Joined: Fri Jun 26, 2020 2:31 pm

Re: Sometimes, cell formatting shows (!) as number whilst it persistently remains (!) text

Post by sft10 » Wed Jul 01, 2020 9:45 am

To clarify this, the problem occurs (= not necessarily, but CAN occur) with cells containing numbers but formatted as text in Excel calc sheets, and then opened in PM.

Then, those fields remain formatted as text (and as such, their content is NOT included in calculations over the cell range they are part of), without that being visible by any means in PM.

First checking the cell by control-1 will show the cell as "text", and if you then change the format to "default" or to "number", further checks by control-1 will show the cell as "default" or as "number", so you think the format change in the control-1 dialog was successful, but in fact, the format of the cell REMAINS "text", and the number in the cell is NOT included in the calculation.

So this is a bug in PM since the dialog, on further tries for the same cell, should not falsely show the cell's format changed to default/number when in fact, it remains text: the dialog should indicate that PM has in fact NOT been able to change the cell format.

It would even be better if PM had a function showing the wrong format (a view toggle perhaps? but perhaps there is such a function, which I simply have not discovered?) by a visual indicator for the cell in the table view, as there is that green triangle in Excel indicating "number formatted as text".

TO CLARIFY THIS FURTHER, above I said I was able to change that cell format in Excel (only, and without problems over there), and afterwards, the cell was recognized and treated correctly in PM, too - this refers to a COPY of the problem calc sheet, so the "original" calc sheet where now PM shows (in the control-1 dialog) "number" format for the cell but which is always treated as "text" (and thus NOT recognized as "number") is always there, and thus could be analyzed by your programmers if you want to address this bug.

SuperTech
SoftMaker Team
SoftMaker Team
Posts: 1428
Joined: Wed Mar 11, 2020 5:31 pm

Re: Sometimes, cell formatting shows (!) as number whilst it persistently remains (!) text

Post by SuperTech » Sat Jul 04, 2020 9:52 am

Thanks for reporting this problem. Please share the associated file which is showing incorrectly in PlanMaker and working fine in Excel so that we can do further investigation for resolution.

Post Reply

Return to “FreeOffice PlanMaker 2018 for Windows”