Sum function problem

[Mariya]

Hey guys,

I have a problem with a file. Look at the attachment below. The sum function does not work. As I could do anything to solve it, I copied everything in Word Pad, then set the cells in General format, pasted them back. The sum function did not work, unles with the first column of numbers. I changed the format in number, the result was the same. I moved the cells and tried with left function, again the same result. Where is the problem according to you? The file is smal and I can make just a data entry on separate file but I want to manage with this one. By the way the file, which I have worked on and attached it for information in a copy of the original one.

Thanks,
Mariya

 

[AlanSidman]

In E3 change your expression to =LEFT(L3, 4)*1. Use this format in each of your expressions. The Left, Right and Mid functions change the data to text. You need it to be a number for the sum to work.

 

[bosco_yip]

Or…......................

1] In E3, copied down :

=IF(L3="","",IF(N(L3),L3,0+LEFT(SUBSTITUTE(L3,",","."),LEN(L3)-2)))

2] In G3, copied across and down :

=IF(M3="","",IF(N(M3),M3,0+LEFT(SUBSTITUTE(M3,",","."),LEN(M3)-2)))

Regards
Bosco

 

[Mariya]

AlanSidman and bosco_yip, thank you for the prompt reply. Both of your advices helped me. As Alan was first, I followed the advice and it happened. When I read Bosco's advice, I tried with it as well, it worked. Thank you very much guys, you are amazing!

Regards,
Mariya

 

[Peter Bartholomew]

A slightly different presentation. I first defined 'rawData' to be a single (relative) column of your data. Then I developed the formula that would 'clean' the data
= IF( ISTEXT(rawData), VALUE( SUBSTITUTE( rawData, "лв", "" ) ), rawData )

ISTEXT is used to ensure that SUBSTITUTE is not applied to numbers or blank cells. Once working, a new defined name 'data' was introduced to refer to the formula. The formula that appears on the worksheet is then
= SUM( data )

Note: The #VALUE! error is due to the comma that appeared in place of the full stop (period) as a decimal separator.

 

[Mariya]

Thank you very much Peter, your solution is very interesting, it would never occurs to me!