if cell has a number calculate formula

[Thomas Martin]

can anyone help me with formula?

if cell range I6:I1000 has a 2 in it the calculate =AGGREGATE(16,7,$AL$6:$AL$1001,0.9)

 

[Thomas Martin]

I appreciate everyone patience with me;
I have rows of times that I am calculating the 90th percentile with the formula =AGGREGATE(16,7,$AL$7:$AL$1000,0.9) and it works properly by its self
what I am looking to do is it only calculate the formula "=AGGREGATE(16,7,$AL$7:$AL$1000,0.9)" with rows that has a 2 in column I
and post the result in a cell I choose.

 

[Thomas Martin]

https://www.dropbox.com/s/hf6sfhsu2qao82r/drop box metrics Response data.xlsx?dl=0
I have uploaded a link to the data file

 

[Ingolf]

may be....

=IF($I7=2,AGGREGATE(16,7,$AL$7:$AL$1000,0.9),"")

 

[Thomas Martin]

Ingolf it did not work, =IF($I7=2,AGGREGATE(16,7,$AL$7:$AL$1000,0.9),"")

 

[Ingolf]

Where - in what cell - you want to put this formula?

Explain exactly what you need. You want to put formula in one cell then drag down?

 

[Thomas Martin]

INGOLF I did get it to work as you have it written if I enter 2 in cell I7 but I want it to also calculate I7 to I1000
I tried by adding in =IF($I7:$I1000=2,AGGREGATE(16,7,$AL$7:$AL$1000,0.9),"") but that can back with a blank result...

 

[Ingolf]

Where - in what cell - you want to put this formula?

Explain exactly what you need. You want to put formula in one cell then drag down?

Try to explain better....


In a sheet put 10 numbers (and number 2 also) and write what answer do you want ...in a cell or drag down formula?

 

[Joe4]

Ingolf,

I think this is the key piece you missed:

what I am looking to do is it only calculate the formula "=AGGREGATE(16,7,$AL$7:$AL$1000,0.9)" with rows that has a 2 in column I

It sounds to me that within the Aggregate function calculation, they only want to include rows where column I is equal to 2.
I really do not have any experience using the AGGREGATE function, so I kind of bowed out.

I agree that it may be clearer if they could post a small example (I asked for that too back in post 10).

 

[Thomas Martin]

in this block of cells I want all rows that has a 2 in column B to be calculated with AGGREGATE(16,7,$C$3:$C$10,0.9),"")
and post the results in cell A2

I just want the "2" to be the trigger, if there is a 1000 rows only include the row with the "2" in the calculation
[TABLE="width: 153"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: yellow, align: right"]5.3
[/TD]
[TD="width: 30, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 26, bgcolor: transparent"][/TD]
[TD="width: 23, bgcolor: transparent"][/TD]
[TD="width: 24, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ingolf]

I don't understand....
If you need help, help as to help you.
Put that example in a file and write also the answer you want....where you want...