batch SUM of LEFT and RIGHT values
 

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

batch SUM of LEFT and RIGHT values
 

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

batch SUM of LEFT and RIGHT values
 

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

batch SUM of LEFT and RIGHT values
 

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

batch SUM of LEFT and RIGHT values
 

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