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

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

--
Biff
Microsoft Excel MVP


"memilanuk" wrote in message
...
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

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

You can also simply use Text to Column function, with the - as the delimiter
to parse your data into two columns
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"memilanuk" wrote:

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

Well, what makes this complicated is because the cells aren't in a
contiguous range. Much of the formula is dedicated to dealing with just that
one aspect of the problem. If the cells were in a contiguous range this
would be relatively simple.

So, if there was a way that you could rearange your layout and put these
cells in a contiguous range it would make things easier.

As is, this formula really isn't any better than the one you're using now.

--
Biff
Microsoft Excel MVP


"memilanuk" wrote in message
...
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