Calc, =left not working as expected

users
#1

Hello

Could someone tell me whats going on in the spreadsheet i uploaded

This is a small part (to demonstrate) of a couple of legacy (by year)
spreadsheets

Column C contains a 7 cell drop down list with a range to select. these
rangers did originally contain a graphic face (seemed like a good idea at
the time) but that corrupted during subsequent Libreoffice updates. Now most
of the faces or just dots
The spreadsheet i use now just contains a range of just numbers - works ok.

But i need to get some of the info from these old sheets into a combination
sheet

Altering the original sheets as little as possible i used =LEFT(cell ref,1)
This seemed to work, except the values returned aren't seen by vlookup and
can not be summed (but can be added) and dont work in a graph

Maybe its something to do with text rather than number values ?

Any suggestions

left_test.ods
<http://nabble.documentfoundation.org/file/n4120882/left_test.ods>

#2

Could someone tell me what's going on in the spreadsheet i uploaded
[...]
Altering the original sheets as little as possible i used =LEFT(cell ref,1) This seemed to work, except the values returned aren't seen by vlookup and can not be summed (but can be added) and don't work in a graph. Maybe its something to do with text rather than number values?

Exactly so.

Any suggestions

Either:
use =VALUE(LEFT(B3;1)) in place of =LEFT(B3;1) - and probably format the cells suitably
or:
use =SUM(VALUE(C3:C9)) instead of =SUM(C3:C9). But this needs to be an array formula, so you need to use Ctrl+Shift+Enter to complete it instead of plain Enter. The formula will appear in the Input Line with braces: {=SUM(VALUE(C3:C9))} - but you cannot type those braces yourself.

I trust this helps.

Brian Barker

#3

"IGraham":

Altering the original sheets as little as possible i used =LEFT(cell ref,1)
This seemed to work, except the values returned aren't seen by vlookup and
can not be summed (but can be added) and dont work in a graph

SUM function skips text values. Use =LEFT(x)+0 or something like that.

#4

Thanks for the replys people
The 'VALUE' convert to number worked - i really should have remembered that,
memory is getting worse