MIN,MAX and average based on given range

amekkeh

New Member
Joined
Oct 18, 2006
Messages
3
Helo,
I have (a hope) a simple question.

In Column A i have values say 1,2,4,6,7 etc
In column B I also have random values.

In cell c1 i have a lower limit say 4
in cell d1 i have an upper limit say 9

I want the min, max and average of column B based on the criteria
given in the cell c1&d2.

So in this case if the value in column a is between 4 and 9 i want to have
the min, max or average of column b. (if a2 and a6 are within the range i want the min/max or average of b2 and b6)

Thanks

Bron
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Welcome to the board.

I think you could write an if satement that would return the value or a blank for a column c (assuming that you move your cells c1 and d1 to d1 and e1).

It would look something like this.

=if(and(a1<d1,a1>e1),b1."")

Then you can use the average, max and min functions on this column to get your answer. Double check that average does indeed leave out the blanks which I think it does (but not positive).

Good Luck

Hayden
 
Upvote 0
Kirsten

Thanks, but it works only half.
I copy it to my excel sheet.
But I get on B7,B8,b9 --> #VALUE

But if I click on B7 and then on "fx" I see the correct value on the
"function argument" screen.
I tried also ctrl+shift+enter.

But if close iI see again #value

Any idea?

Thanks

Bron
 
Upvote 0
I have figure it out. thanks.
I used ctrl+shift+enter on the wrong location

Thanks anyway
Bron
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top