I have multiple rows of data where I want to add the first three entries in
each row and compare the total to the last three entries in the row. However
some of the rows have blank fields so some rows will have differently spaced
first and last three entries. If there are less than six entries then the row
is invalid

Here is an example

A B C D E F G H I J K
1 5 6 8 4 2 5 7 8 3 1 2 1st 3 = 19,
last 3 = 6
2 - 3 4 2 - 4 4 - 8 9 3 1st 3 = 9,
last 3 = 20
3 4 - - 8 7 - 5 6 - 2 - 1st 3= 19,
last 3 = 13
4 - 1 2 4 - - - 7 2 - - invalid

So I need a couple of fomulas that add the frist three and last three of any
list of data


--
Rich

=SUM(N(OFFSET(A1,0,SMALL(IF(B1:L1<>"",COLUMN(B1:L1)),{1,2,3})-1)))=SUM(N(OFFSET(A1,0,LARGE(IF(B1:L1<>"",COLUMN(B1:L1)),{1,2,3})-1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Rich" <(E-Mail Removed)> wrote in message
newsC77ED9A-AD80-43D3-8492-(E-Mail Removed)...
>I have multiple rows of data where I want to add the first three entries in
> each row and compare the total to the last three entries in the row.
> However
> some of the rows have blank fields so some rows will have differently
> spaced
> first and last three entries. If there are less than six entries then the
> row
> is invalid
>
> Here is an example
>
> A B C D E F G H I J K
> 1 5 6 8 4 2 5 7 8 3 1 2 1st 3 = 19,
> last 3 = 6
> 2 - 3 4 2 - 4 4 - 8 9 3 1st 3 = 9,
> last 3 = 20
> 3 4 - - 8 7 - 5 6 - 2 - 1st 3= 19,
> last 3 = 13
> 4 - 1 2 4 - - - 7 2 - - invalid
>
> So I need a couple of fomulas that add the frist three and last three of
> any
> list of data
>
>
> --
> Rich

Thanks Bob

If I have data on row 1 columns A-L I presume I should paste this formula
into cell M1? If so i get result TRUE or FALSE. I actually need two values in
two cells, one for the sum of the first three entres and one for the last
three.
--
Rich


"Bob Phillips" wrote:

> =SUM(N(OFFSET(A1,0,SMALL(IF(B1:L1<>"",COLUMN(B1:L1)),{1,2,3})-1)))=SUM(N(OFFSET(A1,0,LARGE(IF(B1:L1<>"",COLUMN(B1:L1)),{1,2,3})-1)))
>
> which is an array formula, it should be committed with Ctrl-Shift-Enter, not
> just Enter.
> Excel will automatically enclose the formula in braces (curly brackets), do
> not try to do this manually.
> When editing the formula, it must again be array-entered.
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
>
>
> "Rich" <(E-Mail Removed)> wrote in message
> newsC77ED9A-AD80-43D3-8492-(E-Mail Removed)...
> >I have multiple rows of data where I want to add the first three entries in
> > each row and compare the total to the last three entries in the row.
> > However
> > some of the rows have blank fields so some rows will have differently
> > spaced
> > first and last three entries. If there are less than six entries then the
> > row
> > is invalid
> >
> > Here is an example
> >
> > A B C D E F G H I J K
> > 1 5 6 8 4 2 5 7 8 3 1 2 1st 3 = 19,
> > last 3 = 6
> > 2 - 3 4 2 - 4 4 - 8 9 3 1st 3 = 9,
> > last 3 = 20
> > 3 4 - - 8 7 - 5 6 - 2 - 1st 3= 19,
> > last 3 = 13
> > 4 - 1 2 4 - - - 7 2 - - invalid
> >
> > So I need a couple of fomulas that add the frist three and last three of
> > any
> > list of data
> >
> >
> > --
> > Rich
>
>
>

I think Bob meant:

=SUM(N(OFFSET(A1,0,SMALL(IF(B1:L1<>"",COLUMN(B1:L1)),{1,2,3})-1)))

=SUM(N(OFFSET(A1,0,LARGE(IF(B1:L1<>"",COLUMN(B1:L1)),{1,2,3})-1)))

but need extra test:

=IF(COUNT(A2:K2)>=6,SUM(N(OFFSET(A2,0,SMALL(IF(A2:K2<>"",COLUMN(A2:K2)),{1,2,3})-1))),"")

=IF(COUNT(A2:K2)>=6,SUM(N(OFFSET(A2,0,LARGE(IF(A2:K2<>"",COLUMN(A2:K2)),{1,2,3})-1))),"")

HTH

"Rich" wrote:

> Thanks Bob
>
> If I have data on row 1 columns A-L I presume I should paste this formula
> into cell M1? If so i get result TRUE or FALSE. I actually need two values in
> two cells, one for the sum of the first three entres and one for the last
> three.
> --
> Rich
>
>
> "Bob Phillips" wrote:
>
> > =SUM(N(OFFSET(A1,0,SMALL(IF(B1:L1<>"",COLUMN(B1:L1)),{1,2,3})-1)))=SUM(N(OFFSET(A1,0,LARGE(IF(B1:L1<>"",COLUMN(B1:L1)),{1,2,3})-1)))
> >
> > which is an array formula, it should be committed with Ctrl-Shift-Enter, not
> > just Enter.
> > Excel will automatically enclose the formula in braces (curly brackets), do
> > not try to do this manually.
> > When editing the formula, it must again be array-entered.
> >
> > --
> > ---
> > HTH
> >
> > Bob
> >
> > (there's no email, no snail mail, but somewhere should be gmail in my addy)
> >
> >
> >
> > "Rich" <(E-Mail Removed)> wrote in message
> > newsC77ED9A-AD80-43D3-8492-(E-Mail Removed)...
> > >I have multiple rows of data where I want to add the first three entries in
> > > each row and compare the total to the last three entries in the row.
> > > However
> > > some of the rows have blank fields so some rows will have differently
> > > spaced
> > > first and last three entries. If there are less than six entries then the
> > > row
> > > is invalid
> > >
> > > Here is an example
> > >
> > > A B C D E F G H I J K
> > > 1 5 6 8 4 2 5 7 8 3 1 2 1st 3 = 19,
> > > last 3 = 6
> > > 2 - 3 4 2 - 4 4 - 8 9 3 1st 3 = 9,
> > > last 3 = 20
> > > 3 4 - - 8 7 - 5 6 - 2 - 1st 3= 19,
> > > last 3 = 13
> > > 4 - 1 2 4 - - - 7 2 - - invalid
> > >
> > > So I need a couple of fomulas that add the frist three and last three of
> > > any
> > > list of data
> > >
> > >
> > > --
> > > Rich
> >
> >
> >

Toppers

Yes that works, thanks
--
Rich


"Toppers" wrote:

> I think Bob meant:
>
> =SUM(N(OFFSET(A1,0,SMALL(IF(B1:L1<>"",COLUMN(B1:L1)),{1,2,3})-1)))
>
> =SUM(N(OFFSET(A1,0,LARGE(IF(B1:L1<>"",COLUMN(B1:L1)),{1,2,3})-1)))
>
> but need extra test:
>
> =IF(COUNT(A2:K2)>=6,SUM(N(OFFSET(A2,0,SMALL(IF(A2:K2<>"",COLUMN(A2:K2)),{1,2,3})-1))),"")
>
> =IF(COUNT(A2:K2)>=6,SUM(N(OFFSET(A2,0,LARGE(IF(A2:K2<>"",COLUMN(A2:K2)),{1,2,3})-1))),"")
>
> HTH
>
> "Rich" wrote:
>
> > Thanks Bob
> >
> > If I have data on row 1 columns A-L I presume I should paste this formula
> > into cell M1? If so i get result TRUE or FALSE. I actually need two values in
> > two cells, one for the sum of the first three entres and one for the last
> > three.
> > --
> > Rich
> >
> >
> > "Bob Phillips" wrote:
> >
> > > =SUM(N(OFFSET(A1,0,SMALL(IF(B1:L1<>"",COLUMN(B1:L1)),{1,2,3})-1)))=SUM(N(OFFSET(A1,0,LARGE(IF(B1:L1<>"",COLUMN(B1:L1)),{1,2,3})-1)))
> > >
> > > which is an array formula, it should be committed with Ctrl-Shift-Enter, not
> > > just Enter.
> > > Excel will automatically enclose the formula in braces (curly brackets), do
> > > not try to do this manually.
> > > When editing the formula, it must again be array-entered.
> > >
> > > --
> > > ---
> > > HTH
> > >
> > > Bob
> > >
> > > (there's no email, no snail mail, but somewhere should be gmail in my addy)
> > >
> > >
> > >
> > > "Rich" <(E-Mail Removed)> wrote in message
> > > newsC77ED9A-AD80-43D3-8492-(E-Mail Removed)...
> > > >I have multiple rows of data where I want to add the first three entries in
> > > > each row and compare the total to the last three entries in the row.
> > > > However
> > > > some of the rows have blank fields so some rows will have differently
> > > > spaced
> > > > first and last three entries. If there are less than six entries then the
> > > > row
> > > > is invalid
> > > >
> > > > Here is an example
> > > >
> > > > A B C D E F G H I J K
> > > > 1 5 6 8 4 2 5 7 8 3 1 2 1st 3 = 19,
> > > > last 3 = 6
> > > > 2 - 3 4 2 - 4 4 - 8 9 3 1st 3 = 9,
> > > > last 3 = 20
> > > > 3 4 - - 8 7 - 5 6 - 2 - 1st 3= 19,
> > > > last 3 = 13
> > > > 4 - 1 2 4 - - - 7 2 - - invalid
> > > >
> > > > So I need a couple of fomulas that add the frist three and last three of
> > > > any
> > > > list of data
> > > >
> > > >
> > > > --
> > > > Rich
> > >
> > >
> > >

Here's another way, without using the volatile function OFFSET...

For the first three...

=IF(COUNT(A2:K2)>=6,SUM(A2:INDEX(A2:K2,SMALL(IF(A2:K2<>"",COLUMN(A2:K2)-C
OLUMN(A2)+1),3))),"Invalid")

For the last three...

=IF(COUNT(A2:K2)>=6,SUM(INDEX(A2:K2,LARGE(IF(A2:K2<>"",COLUMN(A2:K2)-COLU
MN(A2)+1),3)):K2),"Invalid")

Note that these functions need to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.

Hope this helps!

In article <DC77ED9A-AD80-43D3-8492-(E-Mail Removed)>,
Rich <(E-Mail Removed)> wrote:

> I have multiple rows of data where I want to add the first three entries in
> each row and compare the total to the last three entries in the row. However
> some of the rows have blank fields so some rows will have differently spaced
> first and last three entries. If there are less than six entries then the row
> is invalid
>
> Here is an example
>
> A B C D E F G H I J K
> 1 5 6 8 4 2 5 7 8 3 1 2 1st 3 = 19,
> last 3 = 6
> 2 - 3 4 2 - 4 4 - 8 9 3 1st 3 = 9,
> last 3 = 20
> 3 4 - - 8 7 - 5 6 - 2 - 1st 3= 19,
> last 3 = 13
> 4 - 1 2 4 - - - 7 2 - - invalid
>
> So I need a couple of fomulas that add the frist three and last three of any
> list of data

Thanks

That works as well

Regards
--
Rich


"Domenic" wrote:

> Here's another way, without using the volatile function OFFSET...
>
> For the first three...
>
> =IF(COUNT(A2:K2)>=6,SUM(A2:INDEX(A2:K2,SMALL(IF(A2:K2<>"",COLUMN(A2:K2)-C
> OLUMN(A2)+1),3))),"Invalid")
>
> For the last three...
>
> =IF(COUNT(A2:K2)>=6,SUM(INDEX(A2:K2,LARGE(IF(A2:K2<>"",COLUMN(A2:K2)-COLU
> MN(A2)+1),3)):K2),"Invalid")
>
> Note that these functions need to be confirmed with CONTROL+SHIFT+ENTER,
> not just ENTER.
>
> Hope this helps!
>
> In article <DC77ED9A-AD80-43D3-8492-(E-Mail Removed)>,
> Rich <(E-Mail Removed)> wrote:
>
> > I have multiple rows of data where I want to add the first three entries in
> > each row and compare the total to the last three entries in the row. However
> > some of the rows have blank fields so some rows will have differently spaced
> > first and last three entries. If there are less than six entries then the row
> > is invalid
> >
> > Here is an example
> >
> > A B C D E F G H I J K
> > 1 5 6 8 4 2 5 7 8 3 1 2 1st 3 = 19,
> > last 3 = 6
> > 2 - 3 4 2 - 4 4 - 8 9 3 1st 3 = 9,
> > last 3 = 20
> > 3 4 - - 8 7 - 5 6 - 2 - 1st 3= 19,
> > last 3 = 13
> > 4 - 1 2 4 - - - 7 2 - - invalid
> >
> > So I need a couple of fomulas that add the frist three and last three of any
> > list of data
>