=AVERAGEIF / MAXIFS/MINIFS question

[hissonrr]

Good day all,

I have not really used the =averageif function and was wondering if i could have some help.

Lets say I have the table below....

Basically what I was to do is find the average/min/max of my value 1 column if my depth 3 column fits between the depth 1 and depth 2 column.

For example using the table below I want to find the average of my value 1 column if it falls within a certain depth range from depth 1 and depth 2

If i want to find those values I need 3 formulas that will give me the average/min/max of all values 1 column that have a depth 3 range that fall within the range between cells A2 and B2

the resulting formulas in cells F2, G2, and H2 would produce..

Average of =50
Max of = 100
Min of = 50

can anyone help me out?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Depth 1[/TD]
[TD]Depth 2[/TD]
[TD]--[/TD]
[TD]Depth 3[/TD]
[TD]Value 1[/TD]
[TD]Average[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]50[/TD]
[TD]???[/TD]
[TD]???[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]21[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]31[/TD]
[TD]40[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]41[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



-R

 

[DanteAmor]

Array Formulas

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]F2[/TD]
[TD]=IFERROR(MEDIAN(IF($D$2:$D$6>=$A2,IF($D$2:$D$6<=$B2,$E$2:$E$6))),"")[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD]=IFERROR(MIN(IF($D$2:$D$6>=$A2,IF($D$2:$D$6<=$B2,$E$2:$E$6))),"")[/TD]
[/TR]
[TR]
[TD]H2[/TD]
[TD]=IFERROR(MAX(IF($D$2:$D$6>=$A2,IF($D$2:$D$6<=$B2,$E$2:$E$6))),"")[/TD]
[/TR]
</tbody>[/TABLE]

Note: Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.Note: Do not try and enter the {} manually yourself

Regards Dante Amor