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