[Solved] Numbers as text being included in SUM

[Mnemonic67]

Hi,

I have a column of numbers (e.g. $10.00) my total column sum includes the numbers in the calculation, which is what I want.

The same column has numbers as text, e.g. 10.00 and unfortunately even though the cell is formatted as Text the sum in the total column includes the 10.00 in its calculation. This is what I don't want. I do not want the total to go up from the sum including that "10.00" in the calculation.

This text number entry is necessary as it is entered on the spreadsheet in date order (with a whole lot of other information on the row) and the value "10.00" is actually realised in an adjoining cell on the same row (date) as that $10.00 was originally entered.

This is an example of what I need:

Transaction Date———Transaction———Transfer————Destination———Price———volume——market——Traded———Bought———Sold——————P/L————Total
23-01-18 18:54—————Buy———————Bank——————Other Bank———$2.00———0.0056———AUD————10————$20.00——-$10.00**——-$10.00———$10.00———total was $20.00 before the 10.00* realised as $10.00** was entered under Sold
29-01-18 10:10—————Buy———————Bank2—————Other Bank———$20.00———0.0085——-AUD———-—5———-—$100.00————————————————-$110.00
12-02-18 18:54—————Sold———————Bank——————Other Bank———$1.00———0.0056———AUD——-—10———————————10.00*——————————-$110.00———No change in total
15-02-18 01:35—————Buy ———————Bank2—————Other Bank———$39.99———0.0123——AUD———-—1———-—$39.99——-——————————————-$149.99

Your help is appreciated.

TK

[Villeroy]

SUBTOTAL ignores other SUBTOTALs and filtered cells.

Luckily, no formatting attribute is able to change a formula result or cell value.

[Mnemonic67]

SUBTOTAL ignores other SUBTOTALs and filtered cells.

Hi,

What does that mean?

How do I use this information?

How does it prevent the 10.00* being included in the calculation?

More information would be appreciated.

TK

[FJCC]

If the number 10.00 is entered in a cell, formatting the cell as text afterward does not change that value to text. The cell would have to be formatted as text before the 10.00 is entered for it to be treated as text. You should be able to reenter the 10.00, now that the cell is formatted as text, and have it ignored by the SUM function.
Are all these data entered by hand?

[Mnemonic67]

Hi FJCC,

Thanks for your response.

I can see now that if I remove the entry and add it back to the formatted as text field it no longer increases the total.

But...

It now makes the total show #VALUE!

How can I get around this?

In answer to your question:

Are all these data entered by hand?

A few fields are. Such as when an item is sold the entry needs to be put in next to the original entry (Which is why I am trying to sort this issue). Most come from a CSV import.

TK

[FJCC]

Can you upload a small file showing the problem? It just needs to have a couple of rows of data. To upload a file, click Post Reply and look for the Upload Attachment tab under the box where you type a response.

[Mnemonic67]

Hi FJCC,

Thanks for responding again.

I had to keep working away at this as I wanted to mark it of my to-do list for the day. I searched the forum for different ways of explaining my problem and keywords. I googled to no avail and I then went and looked up "SUBTOTAL" in the not so helpful help files for OOC. It showed the following:

Not very helpful in the way of how it can be used but I played around with various attempts at creating a formula that would work. I had a few different errors along the way - 502, 511, 519, 525.

I came up with =SUBTOTAL(9;AC57+Q58)-SUBTOTAL(9;AG58+Y58)+SUBTOTAL(9;R58)

So that translates to the example table below as In the Total Column =SUBTOTAL(9;Total+Bought)-SUBTOTAL(9;Fee+Withdrawn)+SUBTOTAL(9;Sold) The "Sold" part of the formula is the part where the text "10.00*" is that is realised in the P/L column on the first entry.

Transaction Date———Transaction———Transfer————Destination———Price———volume——market——Traded———Bought———Sold——————P/L——Withdrawn——Total————Fee
23-01-18 18:54—————Buy———————Bank——————Other Bank———$2.00———0.0056———AUD————10————$20.00——-$10.00———-$10.00—————————-$12.00——-$2.00
29-01-18 10:10—————Buy———————Bank2—————Other Bank———$20.00——-0.0085———AUD———-—5———-—$100.00——————————————————————$122.00——$10.00
12-02-18 18:54—————Sold———————Bank——————Other Bank——-$1.00———0.0056———AUD————10———————————10.00*————————————————-$123.00——$1.00
15-02-18 01:35—————Buy ———————Bank2—————Other Bank——-$39.99——-0.0123———AUD———-—1———-—$39.99——-——————————————$5.00———-$151.98——$3.99

It's is probably not the most efficient way of doing it but after four or five attempts this is what worked.

If you, or anyone, can suggest a cleaner formula I would love to see it. Otherwise I'll mark this as solved tomorrow if there are no responses.

I guess I should thank "Villeroy" for the small, not very helpful, input as it did give me something to try and work with. I will add though, that if just a bit more info was provided it may have saved some time.

TK

[Lupp]

I guess I should thank...

If you want to be polite you should first of all avoid avoidable problems for the community to understand your issue and your situation.
To achieve this:
-1- Do not post images if not the question is explicitly about the view.
-2- Do not post malformated (hard to read) tables as text where relevant information like the type of any entry cannot be extracted.
-3- Do not grumble about AOO or related documents. Well, there are shortcomings, but nobody here has the power to relieve us all of this.
-4- Take the trouble instead, to create a reduced but sufficiently rich version of the document you have the issue with, save it to a file (.ods in this case) and attach the file to your post.

[Mnemonic67]

I guess I should thank...

If you want to be polite you should first of all avoid avoidable problems for the community to understand your issue and your situation.
To achieve this:

Hi Lupp,

-1- Do not post images if not the question is explicitly about the view.

The only image I posted was in relation to the solution. It shows the "SUBTOTAL" function, its Syntax and an example about how you use it. I posted it so that others can see it when they view what the solution was, regardless I'm not too sure how in posting it I was being impolite.

-2- Do not post malformated (hard to read) tables as text where relevant information like the type of any entry cannot be extracted.

Well the mock-up of the table I posted clearly indicated the entry with 10.00*, $10.00** and $10.00 (The colouring indicating to what I was referring.) under the column heading "Sold" just near the "Total" column, to which I also referred, with the explanation "total was $20.00 before the 10.00* realised as $10.00** was entered under Sold"

At the time I thought this was the way to go about it, again not too sure how in doing this I was being impolite. But as per my response to your 4th point I will post an actual ods document in future.

-3- Do not grumble about AOO or related documents. Well, there are shortcomings, but nobody here has the power to relieve us all of this.

I'm not really sure how grumbling about them is being impolite...

In the searches I did while trying to find a solution nearly every OP had grumbling about the shortcomings of OO Help.

In checking the Survival Guide viewtopic.php?f=50&t=166 for the forum there is nothing about not being allowed to grumble about help documents.

-4- Take the trouble instead, to create a reduced but sufficiently rich version of the document you have the issue with, save it to a file (.ods in this case) and attach the file to your post.

Noted. I will do that next time.

Thanks so much for your advice Lupp.

TK

[Villeroy]

t92498.ods

(19.82 KiB) Downloaded 63 times

[Mnemonic67]

Thanks Villeroy.

I can see that View>Value Highlighting shows the text.

A good example of what I needed to allow me to experiment.

TK

[RoryOF]

When using Calc, don't ever forget /Value /Highlighting; this facility is the starting point for much Calc debugging.

[Mnemonic67]

Hi RoryOF,

Thanks for your input.

It wasn't that I didn't know there was text in the cells. The problem was that I needed text in a cell that was covered by a formula and because of the use of a + (addition symbol) it wouldn't ignore that text.

Using the SUBTOTAL feature allowed me to get around this.

e.g. =SUBTOTAL(9;AG58+Y58)+SUBTOTAL(9;R58) - R58 was the cell with the text