batch SUM of LEFT and RIGHT values

[memilanuk]

Hello,

I have a relatively simple score-keeping spreadsheet I am working on.
The normal format for the scores to be entered is like '48-1' or
'47-3', etc. Some of the aggregates are the sum of various stages or
matches, which for various reasons are not necessarily in contiguous
blocks or sections on the sheet. As an example, I need to tally up
the values from B3 (48-1), F3 (47-3), and I3 (48-5)... i.e. 48+47+48
'-' 1+3+5.

What I have working thus far is this:

=LEFT(B3,2)+LEFT(F3,2)+LEFT(I3,2)&"-"&RIGHT(B3)+RIGHT(F3)+RIGHT(I3)&"V"
which gives me a total of 143-9V

For one line of three values, its not too bad. For 8-10 lines of
between 5 and 10 values... it starts making me worry about errors
creeping in and causing me grief. Plus I'd like a way to make this
more 'portable' for future use in other sheets.

Is there a way to get SUM() to accept the return value from LEFT() and/
or RIGHT() as input data? I tried it a couple different ways and
couldn't make it work.

Any help would be much appreciated!

TIA,

Monte

 

[T. Valko]

Assuming *every* cell referenced will contain some entry in the form n-n.
This formula will work but I would seriously consider a redesign of my data
rather than use this:

B3 = 48-1
F3 = 47-3
I3 = 48-5

=SUMPRODUCT(INT(--SUBSTITUTE(T(INDIRECT({"B3","F3","I3"})),"-",".")))&"-"&SUMPRODUCT(-(MID(T(INDIRECT({"B3","F3","I3"})),FIND("-",T(INDIRECT({"B3","F3","I3"}))),10)))

Result = 143-9

 

[memilanuk]

Thanks for the response. I'll have to chew on that one for a bit; I'm
not familiar w/ the use of some of those functions (obviously).

What would you suggest as far as a 'redesign of my data'? At this
point I am mainly replicating a printed form that had to be calculated
by hand. I've seen others work where they use a decimal instead of
the dash, and/or go with a format of NNNN.vvvv and then pick off the
applicable portions, or enter the two portions of the score in
separate cells and then concatenate them at the end for a print copy
of the score sheet. For this one sheet I will probably stick w/ the
format as given, but in the future I will have some more latitude as
far as how the user enters data.

Thanks,

Monte

 

[M Kan]

You can also simply use Text to Column function, with the - as the delimiter
to parse your data into two columns