|
Average
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 |
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 |
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 |
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 |
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 |
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 |
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(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 |
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 |
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(MR OUND(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(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 |
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 |
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" 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 |
On Fri, 8 Jul 2005 12:26:02 -0700, "ynissel"
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 - 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" 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 |
On Mon, 11 Jul 2005 07:47:06 -0700, "ynissel"
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 |
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. |
| All times are GMT +1. The time now is 08:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com