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]

calculate the formula if the cell had a particular number such as 2 in cell range I6:I1000, 2 being the trigger to start calculating...

 

[Thomas Martin]

office 2016

 

[Marcílio_Lobão]

Thomas Martin, Good afternoon.

Try to use:

=IF(COUNTIF(I6:I1000,2)>0, YOUR ANOTHER FORMULA, "")

Is that what you want?

I hope it helps.

 

[Thomas Martin]

Thank you that worked perfectly!

 

[Thomas Martin]

ok so maybe it didn't work... I used =IF(COUNTIF(I7:I1000,2)>0,AGGREGATE(16,7,$AL$7:$AL$1000,0.9))
and it calculates if there is any number in column I7:I1000


I only want it to calculate if there is a number 2 in the column

 

[Joe4]

it calculates if there is any number in column I7:I1000

It shouldn't.

If you look at this part of the equation:

[COLOR=#333333]COUNTIF(I7:I1000,2)[/COLOR]

It is specifically counting the number 2's in that range
and the IF part is checking to see if that count if greater than zero.

If you run that formula I posted above by itself, what does it return?
This will tell you how many 2s it is finding in your range.
If it is greater than zero, it will do your formula.
If it is greater than zero, but you think it should be zero, check again. You may be missing/overlooking the two.
You can find where it is by selecting your range, pressing CTRL-F to bring up find, enter 2 for the value you are looking for, then press the Options button and make sure the "Match entire cell contents" check box is checked, and press "Find Next". This will bring you to the first 2 it is finding in your range.

 

[Scott T]

You left off the false argument so if 2 is not found your formula will return FALSE, If that is what is happening then put in "" to return blank.

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

 

[Thomas Martin]

it returns 4 as a count

But I want the formula to only calculate the cells that are found with a 2 in I7:I1000

 

[Joe4]

it returns 4 as a count

But I want the formula to only calculate the cells that are found with a 2 in I7:I1000

That is telling you that there are 4 cells in your range that are equal to 2.
Since that is greater than 0, it is doing the calculation you wanted.

Perhaps you weren't too clear on what it was you are looking for.
Rather than doing that exact expression if ANY cells in your range are equal to 2, do you actually mean to only include the rows that are equal to 2 in your calculation?

Perhaps you can clarify it by posting a small, simple example along with the expected results.

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.