Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula- find min value in range, then average that value with one value on each side
Can anyone please help with an excel formula to find the minimum value
in a row range, then average that value with one value on each side? I was currently using this formula to find the 4 smallest values and average them together, but now I want to find the smallest and average consecutive values (one on each side) =AVERAGE(SMALL('Sheet1'!$C$9:$L$9,{1,2,3,4})) Hope this makes sense. Thank you in advance for your help. Wendy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula- find min value in range, then average that value with one value on each side
Assuming values in A1:C20
=AVERAGE(OFFSET($B$1,MATCH(MIN(B1:B20),B1:B20,0)-1,-1,,3)) This finds the min value of Col B, and then averages the values in that row, one either side of Col B. If two min values the same then the first one found is that which gets evaluated. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Wendy" wrote in message om... Can anyone please help with an excel formula to find the minimum value in a row range, then average that value with one value on each side? I was currently using this formula to find the 4 smallest values and average them together, but now I want to find the smallest and average consecutive values (one on each side) =AVERAGE(SMALL('Sheet1'!$C$9:$L$9,{1,2,3,4})) Hope this makes sense. Thank you in advance for your help. Wendy --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.703 / Virus Database: 459 - Release Date: 10/06/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula- find min value in range, then average that value with one value on each side
Thanks for the quick reply, Ken. I think I need to modify this to find the min within a range in 1 ROW, not a column but not sure how. Example, c8:m8 in row 8 only. Then I need to take the values on both sides of it and average the 3 values. I tried substituting the above example into the formua, but it didn't work (see below). Did I miss something? Any other suggestions, would be great. =AVERAGE(OFFSET($C$8,MATCH(MIN(C8:M8),C8:M8,0)-1,-1,,3)) Thanks again Wendy formula- find min value in range, then average that value with one value on each side From: Ken Wright Date Posted: 6/16/2004 2:32:00 PM Assuming values in A1:C20 =AVERAGE(OFFSET($B$1,MATCH(MIN(B1:B20),B1:B20,0)-1,-1,,3)) This finds the min value of Col B, and then averages the values in that row, one either side of Col B. If two min values the same then the first one found is that which gets evaluated. *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find out average even rows in a particular range | Excel Worksheet Functions | |||
Formula to find average of the best 2 of 3 scores | Excel Worksheet Functions | |||
select date range then find average of values in another cell | Excel Worksheet Functions | |||
Array Formula to find Average Return | Excel Discussion (Misc queries) | |||
find an average from a range, utilizing all data 0 | Excel Worksheet Functions |