Date: prev next · Thread: first prev next last
2015 Archives by date, by thread · List index

At 12:23 14/01/2015 -0600, Wade Smart wrote:
I've stumbled into one of those totally frustrating things that you cant see the reason for:

C65=countif(C3:C61, "Lt Blue")
E65=countif(E3:E61, "Lt Blue")
G65=sum(c65+e65) = 2

I have cleared all formatting. I have started from scratch. I opened another sheet and started. I'm not typing the words in, I'm copy and pasting into the cells and for the life of me I can not figure out why the C column shows no counted values. (I have many colors here so it's not just Lt Blue. Its also Red, Green, White, etc.)

At 13:31 14/01/2015 -0600, Wade Smart wrote:
Didn't know it was obsolete ... use it all the time.

Zn=SUM(Xn+Yn) is not obsolete: it's redundant and plain silly.
Zn=Xn+Yn makes sense, as does
Zn=SUM(Xn;Yn) - though this is somewhat verbose for such a simple case. If you think you need
Zn=SUM(Xn+Yn) , why not
Zn=SUM(SUM(SUM(SUM(Xn+Yn)))) ? Do you also use
Zn=PRODUCT(Xn*Yn) for multiplication and
Zn=SUM(Xn;-Yn) for subtraction?

... out of 50 results for say Lt Blue, it's only showing 4. But I found at least one problem. Even though I copy and pasted there is a space at the end of some of the names like "Lt Blue ". Removing that space has helped a lot. I'm guessing there could be a space at the beginning of others.

There are a lot of things you can improve here. If your problem is just trailing spaces, why not just trim any spaces before performing the comparison? Well, here's why not! You could be tempted to use
C65=COUNTIF(TRIM(C3:C61);"Lt Blue")
in place of your first formula. But the TRIM() function cannot take a range as its argument, so that doesn't work. But what you can do is to enter exactly that into the cell but then complete the entry by pressing not just Enter but Ctrl+Shift+Enter. This converts the formula into an array formula, and this will achieve what you want. If you look in the Input Line, you will see that the formula has been surrounded by braces: {C65=COUNTIF(TRIM(C3:C61);"Lt Blue")} - but note that you cannot achieve the same result by typing the braces yourself.

But there are much better ways to improve the reliability of your spreadsheet. Clearly your ranges C3:C61 and E3:E61 are intended to contain one of a small number of values - in this case, colours. o Instead of typing or even cutting and pasting these values, first create a separate list of them. o My choice would then be to give the range of cells containing the colour names a "name" using Insert | Names > | Define..., but this is not essential. o Now select the range of cells to receive the colour names, e.g. C3:C61, and go to Data | Validity... | Criteria. o For Allow, select "Cell range"; for Source, either type the list name created earlier or drag over the list of colours.
o Tick "Show selection list".
o On the Error Alert tab, tick "Show error message when invalid values are entered".

Now, when you select a cell in the range for data entry, you can type text but you will not be allowed to append the problematic trailing spaces. Better still, notice the down-arrow adjacent to the cell. Click this and select the colour value you require from the menu that is revealed.

You can sensibly also modify your formulae to contain a reference to one cell of the colour list instead of a literal text value such as "Lt Blue". All the data values and the comparison values in formulae will then be derived from the same single entry, which you will take care to spell correctly and not to have any trailing blanks.

Incidentally, the above will not correct existing values, of course, but is the way to continue to modify your spreadsheet and to develop more reliable and less puzzling spreadsheets in the future.

I trust this helps.

Brian Barker

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.