# MIN,MAX and average based on given range

#### amekkeh

##### New Member
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### hayden

##### Board Regular
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

#### Von Pookie

##### MrExcel MVP
You can do this with array formulas.

These are array formulas, so you have to enter them using Ctrl+Shift+Enter in order for them to work correctly.
Book1
ABCD
11849
222
3410
465
576
6
7Min4
8Max8
9Average6
Sheet1

#### amekkeh

##### New Member
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

#### amekkeh

##### New Member
I have figure it out. thanks.
I used ctrl+shift+enter on the wrong location

Thanks anyway
Bron

Replies
8
Views
144
Replies
4
Views
91
Replies
1
Views
872
Replies
6
Views
83
Replies
3
Views
80

1,141,591
Messages
5,707,277
Members
421,499
Latest member
Dpbj

### 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.

### Which adblocker are you using?

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

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