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

This is working after a couple of simple tests:
=AVERAGE(IF(D1:D6MIN(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

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:D6MIN(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

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:D6MIN(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

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(MR OUND(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:D6MIN(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

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(MR OUND(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:D6MIN(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

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(MR OUND(A3,0.125),Winter30,2,0),{1,2,3}))

Biff

"ynissel" wrote in message
...
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(MR OUND(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:D6MIN(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

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" wrote in message
...
| Is there a function to average a series of numbers but dropping the lowest
?
| Thanks,
| Yosef

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" wrote in message
...
| Is there a function to average a series of numbers but dropping the lowest
?
| Thanks,
| Yosef

On Wed, 6 Jul 2005 15:49:03 -0700, "ynissel"
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

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(MR OUND(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"
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

On Thu, 7 Jul 2005 09:08:04 -0700, "ynissel"
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(MR OUND(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

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" wrote in message
...
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(MR OUND(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"
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

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" wrote in message
...
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(MR OUND(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"
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

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" wrote in message
...
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" wrote in message
...
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(MR OUND(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"
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