formula- find min value in range, then average that value with one value on each side

[Wendy]

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

 

[Ken Wright]

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.

 

[Wendy Rosebury]

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

Re: 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!