Average

G

Guest

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

Guest

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.
 
S

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

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

Guest

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

Guest

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 !
 
G

Guest

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
 
G

Guest

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?
 
B

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
 
R

Ron Rosenfeld

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

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
 
G

Guest

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
 
G

Guest

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
 
R

Ron Rosenfeld

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))
Click to expand...

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.
Click to expand...

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 ?
Click to expand...

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
 
B

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
 
G

Guest

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
 
B

Biff

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

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
 
G

Guest

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 !!
 
R

Ron Rosenfeld

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 !!
Click to expand...

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
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

remove lowest 2 values in a row? 2
=average - eliminate the top and bottom number 5
Formula for 6 lowest #'s in a range then average of them. 2
Averages 7
Interesting situation help required!! 3
If Function 1
Average Formula Help 2
Excel Issue with "Average" showing in Status Bar 2

Top