Average

[ynissel]

Is there a function to average a series of numbers but dropping the lowest ?
Thanks,
Yosef

[tjtjjtjt]

This is working after a couple of simple tests:
=AVERAGE(IF(D1:D6>MIN(D1:D6),D1:D6))
Use Ctrl+Shift+Enter to input this formula. It will return #VALUE if you
only press enter.
--
tj


"ynissel" wrote:

> Is there a function to average a series of numbers but dropping the lowest ?
> Thanks,
> Yosef

[ScottO]

Take a look at the function TrimMean - refer to in-built help for syntax.
It does the same as average, but gives you the ability to set what % of
"extreme" values to ignore.
This may not suit your purpose if you only want to omit the one lowest
value, but worth a look anyway.

Rgds,
ScottO

"ynissel" <ynissel@discussions.microsoft.com> wrote in message
news:60D2D8E1-E7D6-4A58-8C3D-AF468A72B868@microsoft.com...
| Is there a function to average a series of numbers but dropping the lowest
?
| Thanks,
| Yosef

[ynissel]

Wow - I cant believe that worked - but I dont understand the formula. CAn
you try to explain ?
Thanks,
Yosef

"tjtjjtjt" wrote:

> This is working after a couple of simple tests:
> =AVERAGE(IF(D1:D6>MIN(D1:D6),D1:D6))
> Use Ctrl+Shift+Enter to input this formula. It will return #VALUE if you
> only press enter.
> --
> tj
>
>
> "ynissel" wrote:
>
> > Is there a function to average a series of numbers but dropping the lowest ?
> > Thanks,
> > Yosef

[ynissel]

That works but ecludes the top and bottom %. Ie if you have 4 values - it
will only average the middle two if you use 25% trim.

But thanks for the try !

"ScottO" wrote:

> Take a look at the function TrimMean - refer to in-built help for syntax.
> It does the same as average, but gives you the ability to set what % of
> "extreme" values to ignore.
> This may not suit your purpose if you only want to omit the one lowest
> value, but worth a look anyway.
>
> Rgds,
> ScottO
>
> "ynissel" <ynissel@discussions.microsoft.com> wrote in message
> news:60D2D8E1-E7D6-4A58-8C3D-AF468A72B868@microsoft.com...
> | Is there a function to average a series of numbers but dropping the lowest
> ?
> | Thanks,
> | Yosef
>
>
>

[tjtjjtjt]

It compares each value to the minimum value in the range.
Values not greater than the minimum are ignored.
I meant to mention that the formula will return #DIV/0 if all the numbers in
the range are the same.

This article will explain it. Also, try a web search for Array Formulas in
Excel.
http://office.microsoft.com/en-us/as...872901033.aspx
--
tj


"ynissel" wrote:

> Wow - I cant believe that worked - but I dont understand the formula. CAn
> you try to explain ?
> Thanks,
> Yosef
>
> "tjtjjtjt" wrote:
>
> > This is working after a couple of simple tests:
> > =AVERAGE(IF(D1:D6>MIN(D1:D6),D1:D6))
> > Use Ctrl+Shift+Enter to input this formula. It will return #VALUE if you
> > only press enter.
> > --
> > tj
> >
> >
> > "ynissel" wrote:
> >
> > > Is there a function to average a series of numbers but dropping the lowest ?
> > > Thanks,
> > > Yosef

[ynissel]

Thanks this helps a lot - one more follow up.
My range isnt in an order. My current formula is
=AVERAGE(VLOOKUP(MROUND(A3,0.125)/100,EMC30,2,0),VLOOKUP(MROUND(A3,0.125),Nomura30,2,0),VLOOKUP(MROUND(A3,0.125),UBS30,2,0),VLOOKUP(MROUND(A3,0.125),Winter30,2,0))
Where is is looking up rates in 4 different sheets and averaging. As Im not
too familiar with Arrays (until I read the article) will it work and can you
show me how ?
Thanks,
Yosef


"tjtjjtjt" wrote:

> It compares each value to the minimum value in the range.
> Values not greater than the minimum are ignored.
> I meant to mention that the formula will return #DIV/0 if all the numbers in
> the range are the same.
>
> This article will explain it. Also, try a web search for Array Formulas in
> Excel.
> http://office.microsoft.com/en-us/as...872901033.aspx
> --
> tj
>
>
> "ynissel" wrote:
>
> > Wow - I cant believe that worked - but I dont understand the formula. CAn
> > you try to explain ?
> > Thanks,
> > Yosef
> >
> > "tjtjjtjt" wrote:
> >
> > > This is working after a couple of simple tests:
> > > =AVERAGE(IF(D1:D6>MIN(D1:D6),D1:D6))
> > > Use Ctrl+Shift+Enter to input this formula. It will return #VALUE if you
> > > only press enter.
> > > --
> > > tj
> > >
> > >
> > > "ynissel" wrote:
> > >
> > > > Is there a function to average a series of numbers but dropping the lowest ?
> > > > Thanks,
> > > > Yosef

[tjtjjtjt]

I don't think I understand what you are trying to accomplish. Could you try
to explain exactly what you are doing?

What do you mean "looking up rates in 4 different sheets?" Are EMC30,
Nomura30, etc. Named Ranges?
--
tj


"ynissel" wrote:

> Thanks this helps a lot - one more follow up.
> My range isnt in an order. My current formula is
> =AVERAGE(VLOOKUP(MROUND(A3,0.125)/100,EMC30,2,0),VLOOKUP(MROUND(A3,0.125),Nomura30,2,0),VLOOKUP(MROUND(A3,0.125),UBS30,2,0),VLOOKUP(MROUND(A3,0.125),Winter30,2,0))
> Where is is looking up rates in 4 different sheets and averaging. As Im not
> too familiar with Arrays (until I read the article) will it work and can you
> show me how ?
> Thanks,
> Yosef
>
>
> "tjtjjtjt" wrote:
>
> > It compares each value to the minimum value in the range.
> > Values not greater than the minimum are ignored.
> > I meant to mention that the formula will return #DIV/0 if all the numbers in
> > the range are the same.
> >
> > This article will explain it. Also, try a web search for Array Formulas in
> > Excel.
> > http://office.microsoft.com/en-us/as...872901033.aspx
> > --
> > tj
> >
> >
> > "ynissel" wrote:
> >
> > > Wow - I cant believe that worked - but I dont understand the formula. CAn
> > > you try to explain ?
> > > Thanks,
> > > Yosef
> > >
> > > "tjtjjtjt" wrote:
> > >
> > > > This is working after a couple of simple tests:
> > > > =AVERAGE(IF(D1:D6>MIN(D1:D6),D1:D6))
> > > > Use Ctrl+Shift+Enter to input this formula. It will return #VALUE if you
> > > > only press enter.
> > > > --
> > > > tj
> > > >
> > > >
> > > > "ynissel" wrote:
> > > >
> > > > > Is there a function to average a series of numbers but dropping the lowest ?
> > > > > Thanks,
> > > > > Yosef

[Biff]

Hi!

Try this:

=AVERAGE(LARGE(VLOOKUP(MROUND(A3,0.125)/100,EMC30,2,0),VLOOKUP(MROUND(A3,0.125),Nomura30,2,0),VLOOKUP(MROUND(A3,0.125),UBS30,2,0),VLOOKUP(MROUND(A3,0.125),Winter30,2,0),{1,2,3}))

Biff

"ynissel" <ynissel@discussions.microsoft.com> wrote in message
news:2A55CF83-FF75-45A6-8FD6-2AFF3365B9D9@microsoft.com...
> Thanks this helps a lot - one more follow up.
> My range isnt in an order. My current formula is
> =AVERAGE(VLOOKUP(MROUND(A3,0.125)/100,EMC30,2,0),VLOOKUP(MROUND(A3,0.125),Nomura30,2,0),VLOOKUP(MROUND(A3,0.125),UBS30,2,0),VLOOKUP(MROUND(A3,0.125),Winter30,2,0))
> Where is is looking up rates in 4 different sheets and averaging. As Im
> not
> too familiar with Arrays (until I read the article) will it work and can
> you
> show me how ?
> Thanks,
> Yosef
>
>
> "tjtjjtjt" wrote:
>
>> It compares each value to the minimum value in the range.
>> Values not greater than the minimum are ignored.
>> I meant to mention that the formula will return #DIV/0 if all the numbers
>> in
>> the range are the same.
>>
>> This article will explain it. Also, try a web search for Array Formulas
>> in
>> Excel.
>> http://office.microsoft.com/en-us/as...872901033.aspx
>> --
>> tj
>>
>>
>> "ynissel" wrote:
>>
>> > Wow - I cant believe that worked - but I dont understand the formula.
>> > CAn
>> > you try to explain ?
>> > Thanks,
>> > Yosef
>> >
>> > "tjtjjtjt" wrote:
>> >
>> > > This is working after a couple of simple tests:
>> > > =AVERAGE(IF(D1:D6>MIN(D1:D6),D1:D6))
>> > > Use Ctrl+Shift+Enter to input this formula. It will return #VALUE if
>> > > you
>> > > only press enter.
>> > > --
>> > > tj
>> > >
>> > >
>> > > "ynissel" wrote:
>> > >
>> > > > Is there a function to average a series of numbers but dropping the
>> > > > lowest ?
>> > > > Thanks,
>> > > > Yosef

[Ron Rosenfeld]

On Wed, 6 Jul 2005 15:49:03 -0700, "ynissel"
<ynissel@discussions.microsoft.com> wrote:

>Is there a function to average a series of numbers but dropping the lowest ?
>Thanks,
>Yosef

The array formula posted previously will drop ALL of the lowest numbers. So if
you have a range containing the numbers

2
2
3
4
5

that formula will average the 3, 4, and 5 giving a result of 4.

If you only want to drop ONE of the lowest numbers, then use the array formula:

=AVERAGE(LARGE(rng,ROW(INDIRECT("1:"&COUNT(rng)-1))))


--ron

[ynissel]

Im getting an error and I cant figure out where the syntax is wrong.

"Biff" wrote:

> Hi!
>
> Try this:
>
> =AVERAGE(LARGE(VLOOKUP(MROUND(A3,0.125)/100,EMC30,2,0),VLOOKUP(MROUND(A3,0.125),Nomura30,2,0),VLOOKUP(MROUND(A3,0.125),UBS30,2,0),VLOOKUP(MROUND(A3,0.125),Winter30,2,0),{1,2,3}))
>
> Biff
>
> "ynissel" <ynissel@discussions.microsoft.com> wrote in message
> news:2A55CF83-FF75-45A6-8FD6-2AFF3365B9D9@microsoft.com...
> > Thanks this helps a lot - one more follow up.
> > My range isnt in an order. My current formula is
> > =AVERAGE(VLOOKUP(MROUND(A3,0.125)/100,EMC30,2,0),VLOOKUP(MROUND(A3,0.125),Nomura30,2,0),VLOOKUP(MROUND(A3,0.125),UBS30,2,0),VLOOKUP(MROUND(A3,0.125),Winter30,2,0))
> > Where is is looking up rates in 4 different sheets and averaging. As Im
> > not
> > too familiar with Arrays (until I read the article) will it work and can
> > you
> > show me how ?
> > Thanks,
> > Yosef
> >
> >
> > "tjtjjtjt" wrote:
> >
> >> It compares each value to the minimum value in the range.
> >> Values not greater than the minimum are ignored.
> >> I meant to mention that the formula will return #DIV/0 if all the numbers
> >> in
> >> the range are the same.
> >>
> >> This article will explain it. Also, try a web search for Array Formulas
> >> in
> >> Excel.
> >> http://office.microsoft.com/en-us/as...872901033.aspx
> >> --
> >> tj
> >>
> >>
> >> "ynissel" wrote:
> >>
> >> > Wow - I cant believe that worked - but I dont understand the formula.
> >> > CAn
> >> > you try to explain ?
> >> > Thanks,
> >> > Yosef
> >> >
> >> > "tjtjjtjt" wrote:
> >> >
> >> > > This is working after a couple of simple tests:
> >> > > =AVERAGE(IF(D1:D6>MIN(D1:D6),D1:D6))
> >> > > Use Ctrl+Shift+Enter to input this formula. It will return #VALUE if
> >> > > you
> >> > > only press enter.
> >> > > --
> >> > > tj
> >> > >
> >> > >
> >> > > "ynissel" wrote:
> >> > >
> >> > > > Is there a function to average a series of numbers but dropping the
> >> > > > lowest ?
> >> > > > Thanks,
> >> > > > Yosef
>
>
>

[ynissel]

Wow - this is getting more and more complicated. the lowest numbers may be
the same and you are correct I wouldnt want to drop them both. But as the
range I am averaging is in different sheets and a lookup function.
Can you get this to work in that context ?
My current formula is
=AVERAGE(VLOOKUP(MROUND(A3,0.125)/100,EMC30,2,0),VLOOKUP(MROUND(A3,0.125),Nomura30,2,0),VLOOKUP(MROUND(A3,0.125),UBS30,2,0),VLOOKUP(MROUND(A3,0.125),Winter30,2,0))

Where A3 is an interest rate (i.e 6%) and the lookups are interest rate in
column 1 and price in column 2. So Im trying to get the average of the 4
prices but drop the lowest one.

BTW - I tried yours with a simple range and it only returned the highest
number ?

Thanks !
Yosef

"Ron Rosenfeld" wrote:

> On Wed, 6 Jul 2005 15:49:03 -0700, "ynissel"
> <ynissel@discussions.microsoft.com> wrote:
>
> >Is there a function to average a series of numbers but dropping the lowest ?
> >Thanks,
> >Yosef
>
> The array formula posted previously will drop ALL of the lowest numbers. So if
> you have a range containing the numbers
>
> 2
> 2
> 3
> 4
> 5
>
> that formula will average the 3, 4, and 5 giving a result of 4.
>
> If you only want to drop ONE of the lowest numbers, then use the array formula:
>
> =AVERAGE(LARGE(rng,ROW(INDIRECT("1:"&COUNT(rng)-1))))
>
>
> --ron
>

[ynissel]

FYI - I just wrote this out in my explanation to Biff, I know Im not supposed
to post 2X but since you asked...
Im trying to find the average of 4 prices while droping the lowest. The
kicker is that the 4 numbers are lookups.
My formula is :
=AVERAGE(VLOOKUP(MROUND(A3,0.125)/100,EMC30,2,0),VLOOKUP(MROUND(A3,0.125),Nomura30,2,0),VLOOKUP(MROUND(A3,0.125),UBS30,2,0),VLOOKUP(MROUND(A3,0.125),Winter30,2,0))
Where A3 is an interest rate (i.e. 6%) and the lookups are column 1 is
interest rate and column 2 is price.
Thanks,
Yosef

"tjtjjtjt" wrote:

> I don't think I understand what you are trying to accomplish. Could you try
> to explain exactly what you are doing?
>
> What do you mean "looking up rates in 4 different sheets?" Are EMC30,
> Nomura30, etc. Named Ranges?
> --
> tj
>
>
> "ynissel" wrote:
>
> > Thanks this helps a lot - one more follow up.
> > My range isnt in an order. My current formula is
> > =AVERAGE(VLOOKUP(MROUND(A3,0.125)/100,EMC30,2,0),VLOOKUP(MROUND(A3,0.125),Nomura30,2,0),VLOOKUP(MROUND(A3,0.125),UBS30,2,0),VLOOKUP(MROUND(A3,0.125),Winter30,2,0))
> > Where is is looking up rates in 4 different sheets and averaging. As Im not
> > too familiar with Arrays (until I read the article) will it work and can you
> > show me how ?
> > Thanks,
> > Yosef
> >
> >
> > "tjtjjtjt" wrote:
> >
> > > It compares each value to the minimum value in the range.
> > > Values not greater than the minimum are ignored.
> > > I meant to mention that the formula will return #DIV/0 if all the numbers in
> > > the range are the same.
> > >
> > > This article will explain it. Also, try a web search for Array Formulas in
> > > Excel.
> > > http://office.microsoft.com/en-us/as...872901033.aspx
> > > --
> > > tj
> > >
> > >
> > > "ynissel" wrote:
> > >
> > > > Wow - I cant believe that worked - but I dont understand the formula. CAn
> > > > you try to explain ?
> > > > Thanks,
> > > > Yosef
> > > >
> > > > "tjtjjtjt" wrote:
> > > >
> > > > > This is working after a couple of simple tests:
> > > > > =AVERAGE(IF(D1:D6>MIN(D1:D6),D1:D6))
> > > > > Use Ctrl+Shift+Enter to input this formula. It will return #VALUE if you
> > > > > only press enter.
> > > > > --
> > > > > tj
> > > > >
> > > > >
> > > > > "ynissel" wrote:
> > > > >
> > > > > > Is there a function to average a series of numbers but dropping the lowest ?
> > > > > > Thanks,
> > > > > > Yosef

[Ron Rosenfeld]

On Thu, 7 Jul 2005 09:08:04 -0700, "ynissel"
<ynissel@discussions.microsoft.com> wrote:

>Wow - this is getting more and more complicated. the lowest numbers may be
>the same and you are correct I wouldnt want to drop them both. But as the
>range I am averaging is in different sheets and a lookup function.
>Can you get this to work in that context ?
>My current formula is
>=AVERAGE(VLOOKUP(MROUND(A3,0.125)/100,EMC30,2,0),VLOOKUP(MROUND(A3,0.125),Nomura30,2,0),VLOOKUP(MROUND(A3,0.125),UBS30,2,0),VLOOKUP(MROUND(A3,0.125),Winter30,2,0))
>

I don't have time to test more complicated methods, but a simple method would
be to put your four VLOOKUP formulas into a contiguous range on the same sheet,
and then use that range for the rng in the formula I suggested.


>Where A3 is an interest rate (i.e 6%) and the lookups are interest rate in
>column 1 and price in column 2. So Im trying to get the average of the 4
>prices but drop the lowest one.

If you will always be looking at four items, you can simplify the formula I
provided to:

=AVERAGE(LARGE(rng,{1,2,3}))

>
>BTW - I tried yours with a simple range and it only returned the highest
>number ?

That is because you overlooked my statement that that formula is an array
formula. To enter an array formula, you must hold down <ctrl><shift> while
hitting <enter>. Excel will place braces {...} around the formula.

The formula I posted in THIS message does not require that.


--ron

[Biff]

Hi!

=(SUM(VLOOKUP_1,VLOOKUP_2,VLOOKUP_3,VLOOKUP_4)-MIN(VLOOKUP_1,VLOOKUP_2,VLOOKUP_3,VLOOKUP_4))/3

OR, use 4 cells for the individual lookups then:

=AVERAGE(LARGE(A1:A4,{1,2,3}))

Biff

"ynissel" <ynissel@discussions.microsoft.com> wrote in message
news:D1A537C7-E171-4004-BBBE-11B094E13506@microsoft.com...
> Wow - this is getting more and more complicated. the lowest numbers may
> be
> the same and you are correct I wouldnt want to drop them both. But as the
> range I am averaging is in different sheets and a lookup function.
> Can you get this to work in that context ?
> My current formula is
> =AVERAGE(VLOOKUP(MROUND(A3,0.125)/100,EMC30,2,0),VLOOKUP(MROUND(A3,0.125),Nomura30,2,0),VLOOKUP(MROUND(A3,0.125),UBS30,2,0),VLOOKUP(MROUND(A3,0.125),Winter30,2,0))
>
> Where A3 is an interest rate (i.e 6%) and the lookups are interest rate in
> column 1 and price in column 2. So Im trying to get the average of the 4
> prices but drop the lowest one.
>
> BTW - I tried yours with a simple range and it only returned the highest
> number ?
>
> Thanks !
> Yosef
>
> "Ron Rosenfeld" wrote:
>
>> On Wed, 6 Jul 2005 15:49:03 -0700, "ynissel"
>> <ynissel@discussions.microsoft.com> wrote:
>>
>> >Is there a function to average a series of numbers but dropping the
>> >lowest ?
>> >Thanks,
>> >Yosef
>>
>> The array formula posted previously will drop ALL of the lowest numbers.
>> So if
>> you have a range containing the numbers
>>
>> 2
>> 2
>> 3
>> 4
>> 5
>>
>> that formula will average the 3, 4, and 5 giving a result of 4.
>>
>> If you only want to drop ONE of the lowest numbers, then use the array
>> formula:
>>
>> =AVERAGE(LARGE(rng,ROW(INDIRECT("1:"&COUNT(rng)-1))))
>>
>>
>> --ron
>>

[ynissel]

Is it safe to say that the array wont work with a bunch of lookups imbeded in
it ?
The reason Im pushing it is that my forulah got a little more complicated
and I need to use it on a bunch of items - so a lookup would be great - if
not Ill have to do it one of your other ways.
Thanks again for all your help !
Yosef

"Biff" wrote:

> Hi!
>
> =(SUM(VLOOKUP_1,VLOOKUP_2,VLOOKUP_3,VLOOKUP_4)-MIN(VLOOKUP_1,VLOOKUP_2,VLOOKUP_3,VLOOKUP_4))/3
>
> OR, use 4 cells for the individual lookups then:
>
> =AVERAGE(LARGE(A1:A4,{1,2,3}))
>
> Biff
>
> "ynissel" <ynissel@discussions.microsoft.com> wrote in message
> news:D1A537C7-E171-4004-BBBE-11B094E13506@microsoft.com...
> > Wow - this is getting more and more complicated. the lowest numbers may
> > be
> > the same and you are correct I wouldnt want to drop them both. But as the
> > range I am averaging is in different sheets and a lookup function.
> > Can you get this to work in that context ?
> > My current formula is
> > =AVERAGE(VLOOKUP(MROUND(A3,0.125)/100,EMC30,2,0),VLOOKUP(MROUND(A3,0.125),Nomura30,2,0),VLOOKUP(MROUND(A3,0.125),UBS30,2,0),VLOOKUP(MROUND(A3,0.125),Winter30,2,0))
> >
> > Where A3 is an interest rate (i.e 6%) and the lookups are interest rate in
> > column 1 and price in column 2. So Im trying to get the average of the 4
> > prices but drop the lowest one.
> >
> > BTW - I tried yours with a simple range and it only returned the highest
> > number ?
> >
> > Thanks !
> > Yosef
> >
> > "Ron Rosenfeld" wrote:
> >
> >> On Wed, 6 Jul 2005 15:49:03 -0700, "ynissel"
> >> <ynissel@discussions.microsoft.com> wrote:
> >>
> >> >Is there a function to average a series of numbers but dropping the
> >> >lowest ?
> >> >Thanks,
> >> >Yosef
> >>
> >> The array formula posted previously will drop ALL of the lowest numbers.
> >> So if
> >> you have a range containing the numbers
> >>
> >> 2
> >> 2
> >> 3
> >> 4
> >> 5
> >>
> >> that formula will average the 3, 4, and 5 giving a result of 4.
> >>
> >> If you only want to drop ONE of the lowest numbers, then use the array
> >> formula:
> >>
> >> =AVERAGE(LARGE(rng,ROW(INDIRECT("1:"&COUNT(rng)-1))))
> >>
> >>
> >> --ron
> >>
>
>
>

[Biff]

> Is it safe to say that the array wont work with a bunch of lookups imbeded
> in
> it ?

Yes. I don't know what I was thinking on that first attempt I made! Doh!

I don't know of a way to pass an array of lookups to the Large function as
the array argument. Maybe Ron knows a way. He's pretty good!

If you need to add even more lookups I think I would opt for the
intermediate individual lookups than the Average/Large vs the long
Sum-Min/n.

Biff

"ynissel" <ynissel@discussions.microsoft.com> wrote in message
news:170BD3DF-E137-4651-9340-F64BA53F9B44@microsoft.com...
> Is it safe to say that the array wont work with a bunch of lookups imbeded
> in
> it ?
> The reason Im pushing it is that my forulah got a little more complicated
> and I need to use it on a bunch of items - so a lookup would be great -
> if
> not Ill have to do it one of your other ways.
> Thanks again for all your help !
> Yosef
>
> "Biff" wrote:
>
>> Hi!
>>
>> =(SUM(VLOOKUP_1,VLOOKUP_2,VLOOKUP_3,VLOOKUP_4)-MIN(VLOOKUP_1,VLOOKUP_2,VLOOKUP_3,VLOOKUP_4))/3
>>
>> OR, use 4 cells for the individual lookups then:
>>
>> =AVERAGE(LARGE(A1:A4,{1,2,3}))
>>
>> Biff
>>
>> "ynissel" <ynissel@discussions.microsoft.com> wrote in message
>> news:D1A537C7-E171-4004-BBBE-11B094E13506@microsoft.com...
>> > Wow - this is getting more and more complicated. the lowest numbers
>> > may
>> > be
>> > the same and you are correct I wouldnt want to drop them both. But as
>> > the
>> > range I am averaging is in different sheets and a lookup function.
>> > Can you get this to work in that context ?
>> > My current formula is
>> > =AVERAGE(VLOOKUP(MROUND(A3,0.125)/100,EMC30,2,0),VLOOKUP(MROUND(A3,0.125),Nomura30,2,0),VLOOKUP(MROUND(A3,0.125),UBS30,2,0),VLOOKUP(MROUND(A3,0.125),Winter30,2,0))
>> >
>> > Where A3 is an interest rate (i.e 6%) and the lookups are interest rate
>> > in
>> > column 1 and price in column 2. So Im trying to get the average of the
>> > 4
>> > prices but drop the lowest one.
>> >
>> > BTW - I tried yours with a simple range and it only returned the
>> > highest
>> > number ?
>> >
>> > Thanks !
>> > Yosef
>> >
>> > "Ron Rosenfeld" wrote:
>> >
>> >> On Wed, 6 Jul 2005 15:49:03 -0700, "ynissel"
>> >> <ynissel@discussions.microsoft.com> wrote:
>> >>
>> >> >Is there a function to average a series of numbers but dropping the
>> >> >lowest ?
>> >> >Thanks,
>> >> >Yosef
>> >>
>> >> The array formula posted previously will drop ALL of the lowest
>> >> numbers.
>> >> So if
>> >> you have a range containing the numbers
>> >>
>> >> 2
>> >> 2
>> >> 3
>> >> 4
>> >> 5
>> >>
>> >> that formula will average the 3, 4, and 5 giving a result of 4.
>> >>
>> >> If you only want to drop ONE of the lowest numbers, then use the array
>> >> formula:
>> >>
>> >> =AVERAGE(LARGE(rng,ROW(INDIRECT("1:"&COUNT(rng)-1))))
>> >>
>> >>
>> >> --ron
>> >>
>>
>>
>>

[ynissel]

Biff - Thanks - How do I forward to Ron to see if he knows. I did 1/2 of it
with pulling the lookups and then the function - but the other 1/2 is like a
2 dimentianal table - so I ll have to do like 500 seperate lookups !!

"Biff" wrote:

> > Is it safe to say that the array wont work with a bunch of lookups imbeded
> > in
> > it ?
>
> Yes. I don't know what I was thinking on that first attempt I made! Doh!
>
> I don't know of a way to pass an array of lookups to the Large function as
> the array argument. Maybe Ron knows a way. He's pretty good!
>
> If you need to add even more lookups I think I would opt for the
> intermediate individual lookups than the Average/Large vs the long
> Sum-Min/n.
>
> Biff
>
> "ynissel" <ynissel@discussions.microsoft.com> wrote in message
> news:170BD3DF-E137-4651-9340-F64BA53F9B44@microsoft.com...
> > Is it safe to say that the array wont work with a bunch of lookups imbeded
> > in
> > it ?
> > The reason Im pushing it is that my forulah got a little more complicated
> > and I need to use it on a bunch of items - so a lookup would be great -
> > if
> > not Ill have to do it one of your other ways.
> > Thanks again for all your help !
> > Yosef
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> =(SUM(VLOOKUP_1,VLOOKUP_2,VLOOKUP_3,VLOOKUP_4)-MIN(VLOOKUP_1,VLOOKUP_2,VLOOKUP_3,VLOOKUP_4))/3
> >>
> >> OR, use 4 cells for the individual lookups then:
> >>
> >> =AVERAGE(LARGE(A1:A4,{1,2,3}))
> >>
> >> Biff
> >>
> >> "ynissel" <ynissel@discussions.microsoft.com> wrote in message
> >> news:D1A537C7-E171-4004-BBBE-11B094E13506@microsoft.com...
> >> > Wow - this is getting more and more complicated. the lowest numbers
> >> > may
> >> > be
> >> > the same and you are correct I wouldnt want to drop them both. But as
> >> > the
> >> > range I am averaging is in different sheets and a lookup function.
> >> > Can you get this to work in that context ?
> >> > My current formula is
> >> > =AVERAGE(VLOOKUP(MROUND(A3,0.125)/100,EMC30,2,0),VLOOKUP(MROUND(A3,0.125),Nomura30,2,0),VLOOKUP(MROUND(A3,0.125),UBS30,2,0),VLOOKUP(MROUND(A3,0.125),Winter30,2,0))
> >> >
> >> > Where A3 is an interest rate (i.e 6%) and the lookups are interest rate
> >> > in
> >> > column 1 and price in column 2. So Im trying to get the average of the
> >> > 4
> >> > prices but drop the lowest one.
> >> >
> >> > BTW - I tried yours with a simple range and it only returned the
> >> > highest
> >> > number ?
> >> >
> >> > Thanks !
> >> > Yosef
> >> >
> >> > "Ron Rosenfeld" wrote:
> >> >
> >> >> On Wed, 6 Jul 2005 15:49:03 -0700, "ynissel"
> >> >> <ynissel@discussions.microsoft.com> wrote:
> >> >>
> >> >> >Is there a function to average a series of numbers but dropping the
> >> >> >lowest ?
> >> >> >Thanks,
> >> >> >Yosef
> >> >>
> >> >> The array formula posted previously will drop ALL of the lowest
> >> >> numbers.
> >> >> So if
> >> >> you have a range containing the numbers
> >> >>
> >> >> 2
> >> >> 2
> >> >> 3
> >> >> 4
> >> >> 5
> >> >>
> >> >> that formula will average the 3, 4, and 5 giving a result of 4.
> >> >>
> >> >> If you only want to drop ONE of the lowest numbers, then use the array
> >> >> formula:
> >> >>
> >> >> =AVERAGE(LARGE(rng,ROW(INDIRECT("1:"&COUNT(rng)-1))))
> >> >>
> >> >>
> >> >> --ron
> >> >>
> >>
> >>
> >>
>
>
>

[Ron Rosenfeld]

On Fri, 8 Jul 2005 12:26:02 -0700, "ynissel"
<ynissel@discussions.microsoft.com> wrote:

>Biff - Thanks - How do I forward to Ron to see if he knows. I did 1/2 of it
>with pulling the lookups and then the function - but the other 1/2 is like a
>2 dimentianal table - so I ll have to do like 500 seperate lookups !!

When you posted your AVERAGE formula containing the multiple lookups about four
messages ago, I was not able to devise a method of converting the series of
lookup results into an array for the LARGE function to act upon, without either
putting the lookups into a contiguous range; or using VBA.

That is why I suggested putting the LOOKUPS into a contiguous range.

I don't understand enough about the parameters of your problem to advise
regarding the "500 separate lookups". If you post back more info soon, I might
be able to take a look at it, but I'll be away for about three weeks starting
tomorrow, so if you don't see a response in the next 24 hours ...

Best,

--ron

[ynissel]

Ron - I guess I missed you. Enjoy - I hope it vacation !

For anyone else that reads this - I have a table of 9 columns and about 30
rows. I have 5 different companies that fill these ranges with prices and I
want the average price - but dropping the lowest.


"Ron Rosenfeld" wrote:

> On Fri, 8 Jul 2005 12:26:02 -0700, "ynissel"
> <ynissel@discussions.microsoft.com> wrote:
>
> >Biff - Thanks - How do I forward to Ron to see if he knows. I did 1/2 of it
> >with pulling the lookups and then the function - but the other 1/2 is like a
> >2 dimentianal table - so I ll have to do like 500 seperate lookups !!
>
> When you posted your AVERAGE formula containing the multiple lookups about four
> messages ago, I was not able to devise a method of converting the series of
> lookup results into an array for the LARGE function to act upon, without either
> putting the lookups into a contiguous range; or using VBA.
>
> That is why I suggested putting the LOOKUPS into a contiguous range.
>
> I don't understand enough about the parameters of your problem to advise
> regarding the "500 separate lookups". If you post back more info soon, I might
> be able to take a look at it, but I'll be away for about three weeks starting
> tomorrow, so if you don't see a response in the next 24 hours ...
>
> Best,
>
> --ron
>

[Ron Rosenfeld]

On Mon, 11 Jul 2005 07:47:06 -0700, "ynissel"
<ynissel@discussions.microsoft.com> wrote:

>Ron - I guess I missed you. Enjoy - I hope it vacation !
>
>For anyone else that reads this - I have a table of 9 columns and about 30
>rows. I have 5 different companies that fill these ranges with prices and I
>want the average price - but dropping the lowest.
>
>

I'm back. And I did have a good time, thank you.

If you have not solved your problem yet, we can try some more.

What's in each row and column?

Do you need to average by rows? by columns? or by some other determinant?


--ron

[joeu2004@hotmail.com]

ynissel wrote:
> For anyone else that reads this - I have a table of 9 columns and about 30
> rows. I have 5 different companies that fill these ranges with prices and
> I want the average price - but dropping the lowest.

I did not follow the entire thread, but based on the summary above,
is there some reason why the following does not satisfy your needs:

=(SUM(A1:I30)-MIN(A1:I30))/(COUNT(A1:I30)-1)

Caveat: This drops only __one__ instance of the lowest value.
If your intent is to drop __all__ instances of the lowest value,
the above does not meet your needs.