[libreoffice-users] Calc, =left not working as expected
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 - IGraham W764 LibreOffice 4.2.0.4 -- View this message in context: http://nabble.documentfoundation.org/Calc-left-not-working-as-expected-tp4120882.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Calc, =left not working as expected
At 07:51 31/08/2014 -0700, Graham Lovatt wrote:
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
--
To unsubscribe e-mail to: users+unsubscr...@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
