Re: [libreoffice-users] function value not being counted

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: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted