How to terminate a condition (IF) at its first occurance?

  • Thread starter Thread starter Steve Lim
  • Start date Start date
S

Steve Lim

I apologize in advance if this is unclear.

I am new to excel, and trying to do a simple formula. In this case,
its a nested IF formula like the following...

=IF(F3>F$2*5,"5",IF(F3>F$2*4,"Q",IF(F3>F$2*3,"T",IF(F3>F$2*2,"D",""))))

Basically the column on the left checks the adjacent column on the
right to see if its 2, 3 or 4 times more than a fixed value (@ cell
F2). If it is, it will stick a label like D(ouble) or T(riple) etc.
The problem is, the values on the right keep accending.. and the label
is valued as TRUE for all of them.. so I get something like

D
D
D
D
T
T

In the left column.

My question is, how can I make excel just label the *first occurance*
of the doubling and tripling instead of all values that are > than
double etc.

Thanks.
 
You would need to include a countif condition to check if there was a
previous cell containing the candidate later

And(F3>F$2*3,Countif(E$2:E2,"T")=0)

as an example of the kind of condition you set up (assumes the formula is in
column E as you appear to describe).

you would modify all the conditions in your formula to reflect a similar
construct.

Then drag fill down the column.
 
Another IF statement to check the previous cell in the same column.

So, if your formula is in column G starting at G3:-

IF(G2="",(your formula),"")


Regards
BrianB
==============================================
 
Back
Top