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

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

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!