I trying to determine the sum of nine numbers in Row / Column
I12,M12,Q12,u12,y12,ad12,ah12,al12,ap12, minus the two largest numbers of
the nine.

Thanks

Bob

Define a name (Mydata) as L12,M12,Q12,U12,Y12,AD12,AH12,AL12,AP12
=SUM(Mydata)-(LARGE(Mydata,1)+LARGE(Mydata,2))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"robert morris" wrote in message
...

I trying to determine the sum of nine numbers in Row / Column
I12,M12,Q12,u12,y12,ad12,ah12,al12,ap12, minus the two largest numbers of
the nine.

Thanks

Bob

Sample below using Range A1:A10 with the following formula in B1:

=SUM(A1:A10)-LARGE(A1:A10,1)-LARGE(A1:A10,2)

HTH
Jim May


"robert morris" wrote:


I trying to determine the sum of nine numbers in Row / Column
I12,M12,Q12,u12,y12,ad12,ah12,al12,ap12, minus the two largest numbers of
the nine.

Thanks

Bob

Jim,

Thanks for the prompt reply. I could not reply to you as my internet,
phones, every thing went out from the phone company. In any case, I have
tried your help but cannot use it in the range (A1:A10) I need to define
only the sum of the columns of (I12,M12,Q12,U12,Y12,ad12,AH12,AL12,AP12) As
all other Columns have Text and/or Numbers not used. I just need the SUM of
the above Columns minus the two largest numbers from the group. Example;
Number 1+2+3+4+5+6+7+8+9 = 45 Minus the two largest 8 and 9 = Total Net 28
I would then copy this down approximately 100 rows.

Possibly this is a little clearer.

Bob

"Jim May" wrote:

Sample below using Range A1:A10 with the following formula in B1:

=SUM(A1:A10)-LARGE(A1:A10,1)-LARGE(A1:A10,2)

HTH
Jim May


"robert morris" wrote:


I trying to determine the sum of nine numbers in Row / Column
I12,M12,Q12,u12,y12,ad12,ah12,al12,ap12, minus the two largest numbers of
the nine.

Thanks

Bob

Bernard,

Thanks for the prompt reply. Sorry I could not reply sooner but someone cut
cables (during construction) and I have been in and out of phone/internet
service.

I'm afraid I'm a total 101 student in Macros. Is there a way you could help
me from the beginning with yours. I have tried it and failed. I'm running
XP with Office 2007 upgrade.

In a simple 1,2,3,4,5,6,7,8,9 numbering in Colum/Rows noted, the answer
would be 45 minus the two largest of 8 & 9, the net I'm looking for would be
28.

Your help is appreciated.

Bob

"Bernard Liengme" wrote:

Define a name (Mydata) as L12,M12,Q12,U12,Y12,AD12,AH12,AL12,AP12
=SUM(Mydata)-(LARGE(Mydata,1)+LARGE(Mydata,2))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"robert morris" wrote in message
...

I trying to determine the sum of nine numbers in Row / Column
I12,M12,Q12,u12,y12,ad12,ah12,al12,ap12, minus the two largest numbers of
the nine.

Thanks

Bob

On Mon, 20 Aug 2007 09:52:08 -0700, robert morris
wrote:


I trying to determine the sum of nine numbers in Row / Column
I12,M12,Q12,u12,y12,ad12,ah12,al12,ap12, minus the two largest numbers of
the nine.

Thanks

Bob

=SUM(I12,M12,Q12,U12,Y12,AD12,AH12,AL12,AP12)-
SUM(LARGE((I12,M12,Q12,U12,Y12,AD12,AH12,AL12,AP12 ),{1,2}))


--ron

Ron,

Beautiful!! Like a charm. Thanks so much. I'm trying but I have so much
to learn.

Thanks again!

Bob


"Ron Rosenfeld" wrote:

On Mon, 20 Aug 2007 09:52:08 -0700, robert morris
wrote:


I trying to determine the sum of nine numbers in Row / Column
I12,M12,Q12,u12,y12,ad12,ah12,al12,ap12, minus the two largest numbers of
the nine.

Thanks

Bob

=SUM(I12,M12,Q12,U12,Y12,AD12,AH12,AL12,AP12)-
SUM(LARGE((I12,M12,Q12,U12,Y12,AD12,AH12,AL12,AP12 ),{1,2}))


--ron

Ron,

As I posted, works beautifully! One additional question, about conditional
formatting with a red fill highlighting the two highest numbers. I have xp
with Office 2007. I thought i could finish this myself, but it tells me I
cannot use an array in conditional formatting. Of course I could have
entered the formula wrong. Help?

Thanks,

Bob


"Ron Rosenfeld" wrote:

On Mon, 20 Aug 2007 09:52:08 -0700, robert morris
wrote:


I trying to determine the sum of nine numbers in Row / Column
I12,M12,Q12,u12,y12,ad12,ah12,al12,ap12, minus the two largest numbers of
the nine.

Thanks

Bob

=SUM(I12,M12,Q12,U12,Y12,AD12,AH12,AL12,AP12)-
SUM(LARGE((I12,M12,Q12,U12,Y12,AD12,AH12,AL12,AP12 ),{1,2}))


--ron

On Tue, 21 Aug 2007 22:42:01 -0700, robert morris
wrote:

Ron,

As I posted, works beautifully! One additional question, about conditional
formatting with a red fill highlighting the two highest numbers. I have xp
with Office 2007. I thought i could finish this myself, but it tells me I
cannot use an array in conditional formatting. Of course I could have
entered the formula wrong. Help?

Thanks,

Bob


"Ron Rosenfeld" wrote:

On Mon, 20 Aug 2007 09:52:08 -0700, robert morris
wrote:


I trying to determine the sum of nine numbers in Row / Column
I12,M12,Q12,u12,y12,ad12,ah12,al12,ap12, minus the two largest numbers of
the nine.

Thanks

Bob

=SUM(I12,M12,Q12,U12,Y12,AD12,AH12,AL12,AP12)-
SUM(LARGE((I12,M12,Q12,U12,Y12,AD12,AH12,AL12,AP12 ),{1,2}))


--ron


The formula I gave you is considered an array formula since the last term of
the LARGE function is an array constant. To use this in conditional
formatting, you will have to break it out.

I did this.

I defined RNG to be the range of cells you used:

Insert/Name/Define
Names in Workbook: RNG
Refers to:
=$I$12,$M$12,$Q$12,$U$12,$Y$12,$AD$12,$AH$12,$AL$1 2,$AP$12

(Note that the references must be absolute).

Select I12.

Format/Conditional Formatting/Formula Is:
=OR(I12=MAX(RNG),I12=LARGE(RNG,2))

Then copy the format to the other cells with the format painter.

By the way, if you've got the name defined properly, you can simplify the
summation formula to =SUM(RNG)-SUM(LARGE(RNG,{1,2}))


--ron

Ron,

O.K., as a beginner, where do I start to find and enter:
Insert/Name/Define RNG

Names in Workbook: RNG
Refers to:=$I$12,$M$12,$Q$12,$U$12,$Y$12,$AD$12,$AH$12,$A L$12,$AP$12

Is the above entered as a Macro?

Continued help is appreciated. Help me out of this and I won't bother you
further on this matter.

Bob

"Ron Rosenfeld" wrote:

On Tue, 21 Aug 2007 22:42:01 -0700, robert morris
wrote:

Ron,

As I posted, works beautifully! One additional question, about conditional
formatting with a red fill highlighting the two highest numbers. I have xp
with Office 2007. I thought i could finish this myself, but it tells me I
cannot use an array in conditional formatting. Of course I could have
entered the formula wrong. Help?

Thanks,

Bob


"Ron Rosenfeld" wrote:

On Mon, 20 Aug 2007 09:52:08 -0700, robert morris
wrote:


I trying to determine the sum of nine numbers in Row / Column
I12,M12,Q12,u12,y12,ad12,ah12,al12,ap12, minus the two largest numbers of
the nine.

Thanks

Bob

=SUM(I12,M12,Q12,U12,Y12,AD12,AH12,AL12,AP12)-
SUM(LARGE((I12,M12,Q12,U12,Y12,AD12,AH12,AL12,AP12 ),{1,2}))


--ron


The formula I gave you is considered an array formula since the last term of
the LARGE function is an array constant. To use this in conditional
formatting, you will have to break it out.

I did this.

I defined RNG to be the range of cells you used:

Insert/Name/Define
Names in Workbook: RNG
Refers to:
=$I$12,$M$12,$Q$12,$U$12,$Y$12,$AD$12,$AH$12,$AL$1 2,$AP$12

(Note that the references must be absolute).

Select I12.

Format/Conditional Formatting/Formula Is:
=OR(I12=MAX(RNG),I12=LARGE(RNG,2))

Then copy the format to the other cells with the format painter.

By the way, if you've got the name defined properly, you can simplify the
summation formula to =SUM(RNG)-SUM(LARGE(RNG,{1,2}))


--ron

On Wed, 22 Aug 2007 04:50:01 -0700, robert morris
wrote:

Ron,

O.K., as a beginner, where do I start to find and enter:
Insert/Name/Define RNG

Names in Workbook: RNG
Refers to:=$I$12,$M$12,$Q$12,$U$12,$Y$12,$AD$12,$AH$12,$A L$12,$AP$12

Is the above entered as a Macro?

Continued help is appreciated. Help me out of this and I won't bother you
further on this matter.

Bob


On the top bar of Excel (actually the 2nd from top, there are a number of
"Menu" options -- File Edit ...

One of those options should be "Insert"

If you click on "Insert" it opens a dropdown menu. One of those items in the
dropdown menu is "Name"

If you then click on "Name", one of the submenu items will be "Define"

If you then click on "Define" it will open a Dialog box where you will see, and
can fill in, the information.
--ron

Ron,

Sorry about the delay in replying. Thanks ever so much. I have used your
conditional formating for Row 12. Everything works beautifully. I now have
to copy all these rows dow to approximately 150 Rows. Is there a fast way.

Thanks again,

Bob

"Ron Rosenfeld" wrote:

On Wed, 22 Aug 2007 04:50:01 -0700, robert morris
wrote:

Ron,

O.K., as a beginner, where do I start to find and enter:
Insert/Name/Define RNG

Names in Workbook: RNG
Refers to:=$I$12,$M$12,$Q$12,$U$12,$Y$12,$AD$12,$AH$12,$A L$12,$AP$12

Is the above entered as a Macro?

Continued help is appreciated. Help me out of this and I won't bother you
further on this matter.

Bob


On the top bar of Excel (actually the 2nd from top, there are a number of
"Menu" options -- File Edit ...

One of those options should be "Insert"

If you click on "Insert" it opens a dropdown menu. One of those items in the
dropdown menu is "Name"

If you then click on "Name", one of the submenu items will be "Define"

If you then click on "Define" it will open a Dialog box where you will see, and
can fill in, the information.
--ron

Ron,

One other question, after determining the sum of the lowest seven numbers,
the sum must be sorted by lowest to highest while maintaing the color
formating. Is this a problem? Many thanks for all the help. I know I have
asked for a lot.

Bob


"Ron Rosenfeld" wrote:

On Wed, 22 Aug 2007 04:50:01 -0700, robert morris
wrote:

Ron,

O.K., as a beginner, where do I start to find and enter:
Insert/Name/Define RNG

Names in Workbook: RNG
Refers to:=$I$12,$M$12,$Q$12,$U$12,$Y$12,$AD$12,$AH$12,$A L$12,$AP$12

Is the above entered as a Macro?

Continued help is appreciated. Help me out of this and I won't bother you
further on this matter.

Bob


On the top bar of Excel (actually the 2nd from top, there are a number of
"Menu" options -- File Edit ...

One of those options should be "Insert"

If you click on "Insert" it opens a dropdown menu. One of those items in the
dropdown menu is "Name"

If you then click on "Name", one of the submenu items will be "Define"

If you then click on "Define" it will open a Dialog box where you will see, and
can fill in, the information.
--ron

On Thu, 23 Aug 2007 16:08:01 -0700, robert morris
wrote:

Ron,

Sorry about the delay in replying. Thanks ever so much. I have used your
conditional formating for Row 12. Everything works beautifully. I now have
to copy all these rows dow to approximately 150 Rows. Is there a fast way.

Thanks again,

Bob

You will find the HELP function in Excel quite useful, and probably faster and
more clear than posting here in certain areas.

So type "Copy Formatting" into the HELP area. You may also need to change the
definition of rng from absolute to relative, or mixed, but you should be able
to figure out that on your own.

Best wishes,
--ron

On Thu, 23 Aug 2007 16:22:02 -0700, robert morris
wrote:

Ron,

One other question, after determining the sum of the lowest seven numbers,
the sum must be sorted by lowest to highest while maintaing the color
formating. Is this a problem? Many thanks for all the help. I know I have
asked for a lot.

What happened when you tried to do that?
--ron

Ron,

The sum numbers sort perfectly. I have only entered the conditional
formating in Row 12 which also works perfectly. My concern was for the
conditional formating which I have not copied down all the rows as yet, at
least not successfully. Should I delete the absoute reference as to the
additional Rows?

Thanks,

Bob

"Ron Rosenfeld" wrote:

On Thu, 23 Aug 2007 16:22:02 -0700, robert morris
wrote:

Ron,

One other question, after determining the sum of the lowest seven numbers,
the sum must be sorted by lowest to highest while maintaing the color
formating. Is this a problem? Many thanks for all the help. I know I have
asked for a lot.

What happened when you tried to do that?
--ron

On Thu, 23 Aug 2007 17:26:01 -0700, robert morris
wrote:

Ron,

The sum numbers sort perfectly. I have only entered the conditional
formating in Row 12 which also works perfectly. My concern was for the
conditional formating which I have not copied down all the rows as yet, at
least not successfully. Should I delete the absoute reference as to the
additional Rows?

Thanks,

Bob

I think you should experiment and see what happens -- that is what I would have
to do as I'm not sure.

A straight absolute reference will not work.

Just guessing that you'd want a mixed reference, with the columns absolute and
the rows relative when you copy the formats. But I'm not sure if that'll work
when you do the sorting. If it doesn't, then after you copy the mixed
reference conditional formatting, make the whole definition of rng relative.
--ron

Ron,

One last question on this Cond formating. As I said, your solution works
perfectly as to Col/Row I12. I have sorted the row from every angle.
Problem is in trying to copy down to the 150 rows I need. I have tried
changing absolute reference to relative which excel will not allow as it just
returns all to absolute. You said "after I copy the mixed reference
conditional formating, make the whole definition of RNG relative" How do I
make this a "mixed reference" as well as make the whole definition of RNG
relative. I have also searched "help" and found nothing.

Sorry for the stupidity,

Bob


"Ron Rosenfeld" wrote:

On Thu, 23 Aug 2007 17:26:01 -0700, robert morris
wrote:

Ron,

The sum numbers sort perfectly. I have only entered the conditional
formating in Row 12 which also works perfectly. My concern was for the
conditional formating which I have not copied down all the rows as yet, at
least not successfully. Should I delete the absoute reference as to the
additional Rows?

Thanks,

Bob

I think you should experiment and see what happens -- that is what I would have
to do as I'm not sure.

A straight absolute reference will not work.

Just guessing that you'd want a mixed reference, with the columns absolute and
the rows relative when you copy the formats. But I'm not sure if that'll work
when you do the sorting. If it doesn't, then after you copy the mixed
reference conditional formatting, make the whole definition of rng relative.
--ron

On Mon, 27 Aug 2007 21:02:04 -0700, robert morris
wrote:

Ron,

One last question on this Cond formating. As I said, your solution works
perfectly as to Col/Row I12. I have sorted the row from every angle.
Problem is in trying to copy down to the 150 rows I need. I have tried
changing absolute reference to relative which excel will not allow as it just
returns all to absolute. You said "after I copy the mixed reference
conditional formating, make the whole definition of RNG relative" How do I
make this a "mixed reference" as well as make the whole definition of RNG
relative. I have also searched "help" and found nothing.

Sorry for the stupidity,

Bob

The easiest way is to select the Refers To: block in the Insert/Name/Define
dialog, and then use F4 until you get the fixed column; relative row format
(e.g. $I12)

I've not seen the problem of "returning all to absolute".

I have a worksheet I can send you if you have an email address I can use.


--ron

Ron,

Yes, please send the worksheet.

Bob

"Ron Rosenfeld" wrote:

On Mon, 27 Aug 2007 21:02:04 -0700, robert morris
wrote:

Ron,

One last question on this Cond formating. As I said, your solution works
perfectly as to Col/Row I12. I have sorted the row from every angle.
Problem is in trying to copy down to the 150 rows I need. I have tried
changing absolute reference to relative which excel will not allow as it just
returns all to absolute. You said "after I copy the mixed reference
conditional formating, make the whole definition of RNG relative" How do I
make this a "mixed reference" as well as make the whole definition of RNG
relative. I have also searched "help" and found nothing.

Sorry for the stupidity,

Bob

The easiest way is to select the Refers To: block in the Insert/Name/Define
dialog, and then use F4 until you get the fixed column; relative row format
(e.g. $I12)

I've not seen the problem of "returning all to absolute".

I have a worksheet I can send you if you have an email address I can use.


--ron

Ron,

EUREKA !! (excuse the shouting)

Stupid is as stupid does. Made everything absolute Col / relative Row (was
doing this incorectly). Highlights two colors and Sorts perfectly. Could
have never done this without your help. As an aside I am 76 years old and
trying to teach myself Excel. All the things I do is for other people,
groups, so sometimes I get myself in over my head. Again, Thanks so much!

Would love to see the "worksheet" you refered to.

email:

Bob

"Ron Rosenfeld" wrote:

On Mon, 27 Aug 2007 21:02:04 -0700, robert morris
wrote:

Ron,

One last question on this Cond formating. As I said, your solution works
perfectly as to Col/Row I12. I have sorted the row from every angle.
Problem is in trying to copy down to the 150 rows I need. I have tried
changing absolute reference to relative which excel will not allow as it just
returns all to absolute. You said "after I copy the mixed reference
conditional formating, make the whole definition of RNG relative" How do I
make this a "mixed reference" as well as make the whole definition of RNG
relative. I have also searched "help" and found nothing.

Sorry for the stupidity,

Bob

The easiest way is to select the Refers To: block in the Insert/Name/Define
dialog, and then use F4 until you get the fixed column; relative row format
(e.g. $I12)

I've not seen the problem of "returning all to absolute".

I have a worksheet I can send you if you have an email address I can use.


--ron

On Tue, 28 Aug 2007 14:30:04 -0700, robert morris
wrote:

Ron,

EUREKA !! (excuse the shouting)

Stupid is as stupid does. Made everything absolute Col / relative Row (was
doing this incorectly). Highlights two colors and Sorts perfectly. Could
have never done this without your help. As an aside I am 76 years old and
trying to teach myself Excel. All the things I do is for other people,
groups, so sometimes I get myself in over my head. Again, Thanks so much!

Would love to see the "worksheet" you refered to.

Bob,

Glad to help. For me, too, sometimes I have those Eureka moments :-)) I hope
I'm as active at your age as you seem to be (and I don't have that many more
years to go).

I sent the worksheet a few hours ago, but it was just a very simple demo of
what you've already figured out. So no harm if it got lost in cyberspace.
--ron