SUM Returning 0, Calculation Set To Automatic and Cell Formatted as Number

[clattenburg cake]

I have this problem where the SUM function is returning 0. The context is this- I am simulating values using RAND() and looking up the values corersponding to probabilities from a different tables. I then use the TRIM function to return the value I need, and the final number is of a "general" format.

This is the formula in the column I wish to sum:

Please Login or Register  to view this content.

And this is dragged down the column.

When I change the cells using =VALUE(cell) the SUM function then returns the required value. Oddly the SUMPRODUCT function seems to work.

Calculation is set to automatic and the cell has been formatted as a number. Can anyone explain what's happened please?

[martindwilson]

that is because =TRIM(LEFT(AD1,FIND("-",AD1)-1)) returns text numbers
just use any of these
=TRIM(LEFT(AD1,FIND("-",AD1)-1))+0
=TRIM(LEFT(AD1,FIND("-",AD1)-1))*1
=--TRIM(LEFT(AD1,FIND("-",AD1)-1))

[clattenburg cake]

Ta, that's solved it!