What is worng with my formula?

[Guest]

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 Liengme]

Define a name (Mydata) as L12,M12,Q12,U12,Y12,AD12,AH12,AL12,AP12
=SUM(Mydata)-(LARGE(Mydata,1)+LARGE(Mydata,2))
best wishes

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Ron Rosenfeld]

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

 

[Guest]

Ron,

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

Thanks again!

Bob

 

[Guest]

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]

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

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$12,$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

 

[Guest]

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,$AL$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]

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,$AL$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

 

[Guest]

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

 

[Guest]

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]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

Ron,

Yes, please send the worksheet. (e-mail address removed)

Bob