Nested if then statement
 

I want to create a formula that has multiple criteria. For example if cell B2 falls between 0-25% is cell C2 70% or greater. If yes, then count.

Basically I have four requirements for cell B2

0-25% then C2 must be 70% or higher to count
26-35% then C2 must be 75% or higher to count
36-45% then C2 must be 80% or higher to count
46 or greater then C2 must be 90% or higher to count

I'm not sure if I can create one embedded formula or have to write separate formulas for each criteria above.

Thanks for any help/advice!!!

Nested if then statement
 

On Thu, 3 Jan 2013 19:15:27 +0000, Travelgirl333 wrote:


I want to create a formula that has multiple criteria. For example if
cell B2 falls between 0-25% is cell C2 70% or greater. If yes, then
count.

Basically I have four requirements for cell B2

0-25% then C2 must be 70% or higher to count
26-35% then C2 must be 75% or higher to count
36-45% then C2 must be 80% or higher to count
46 or greater then C2 must be 90% or higher to count

I'm not sure if I can create one embedded formula or have to write
separate formulas for each criteria above.

Thanks for any help/advice!!!

In your request, you omit possible values of C2. In particular, you do not state what you want to occur should C2 be greater than 25% and less than 26%; and the same is true at the other boundaries.

If, in fact, you want NOTHING (no count) to occur should that be the case, you will need to add some logic to ensure that. Otherwise, my assumption as to where the boundary should be may be adequate.

=OR(AND(B2<=25%,C2=70%),AND(B2<=35%,C2=75%),AND( B2<=45%,C2=80%),AND(B245%,C2=90%))

will return TRUE or FALSE depending on whether the condition is met.
If you preface the formula with a double unary, it will return 1 or 0 and be amenable to "counting":

=--OR(AND(B2<=25%,C2=70%),AND(B2<=35%,C2=75%),AND(B 2<=45%,C2=80%),AND(B245%,C2=90%))

Nested if then statement
 

"Travelgirl333" wrote:
I want to create a formula that has multiple criteria. For example if
cell B2 falls between 0-25% is cell C2 70% or greater. If yes, then
count. Basically I have four requirements for cell B2
0-25% then C2 must be 70% or higher to count
26-35% then C2 must be 75% or higher to count
36-45% then C2 must be 80% or higher to count
46 or greater then C2 must be 90% or higher to count
I'm not sure if I can create one embedded formula or have to write
separate formulas for each criteria above.

Not sure what you mean by "to count" when talking about a single pair of
cells, B2 and C2.

If you mean that you are looking at a range of rows, for example B2:B100 and
C2:C100, and you want to count the number of pairs that qualify, then:

=SUMPRODUCT(--((B2:B100<=25%)*(C2:C100=70%)
+ (B2:B10025%)*(B2:B100<=35%)*(C2:C100=75%)
+ (B2:B10035%)*(B2:B100<=45%)*(C2:C100=80%)
+ (B2:B10045%)*(C2:C100=90%)0))

or

=SUMPRODUCT(--((B2:B100<26%)*(C2:C100=70%)
+ (B2:B100=26%)*(B2:B100<36%)*(C2:C100=75%)
+ (B2:B100=36%)*(B2:B100<46%)*(C2:C100=80%)
+ (B2:B100=46%)*(C2:C100=90%)0))

The choice depends on your intended meaning of the ranges 0-25% v. 26-35%.
Note that there are an "infinite" number of values between 25% and 26%
unaccounted for, unless you ensure that B2 is rounded to 2 decimal places.