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

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
Joined
Sep 23, 2005
Messages
159
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
 

amekkeh

New Member
Joined
Oct 18, 2006
Messages
3
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
Joined
Oct 18, 2006
Messages
3
I have figure it out. thanks.
I used ctrl+shift+enter on the wrong location

Thanks anyway
Bron
 

Forum statistics

Threads
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.
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
Top