intent: use 12 variables to get 3 products - 12, or 16, or 24

this formula works

=IF(A4="2 x 6 12 in centers",12,IF(A4="2 x 8 12 in centers",12,
IF(A4="2 x 10 12 in centers",12,IF(A4="2 x 12 12 in centers",12))))

but I have 8 more variables to go ending with ("2 x 12 24 in centers",24)
and the equation is going to be too long.

I tried using the special characters question mark "?" and asterik "*" but
got nowhere.

Any help out there? thank you - Chris, a swell estimator

Excel will only allow for a maximum of 7 nested "IFs" in a formula... Not
knowing the full extent of your worksheet from the details presented , you
might try to use a Vlookup table, Column A contains the criteria and Column B
contains the value that you desire, then have you formula look up the desire
value...

Column "A" Column "B"
2 x 6 12 in centers 12
2 x 8 12 in centers 12
2 x 10 12 in centers 12
--

Joe Mac


"swell estimator" wrote:

intent: use 12 variables to get 3 products - 12, or 16, or 24

this formula works

=IF(A4="2 x 6 12 in centers",12,IF(A4="2 x 8 12 in centers",12,
IF(A4="2 x 10 12 in centers",12,IF(A4="2 x 12 12 in centers",12))))

but I have 8 more variables to go ending with ("2 x 12 24 in centers",24)
and the equation is going to be too long.

I tried using the special characters question mark "?" and asterik "*" but
got nowhere.

Any help out there? thank you - Chris, a swell estimator

Try this:

=--MID(A4,FIND("in",A4)-3,2)


"swell estimator" wrote:

intent: use 12 variables to get 3 products - 12, or 16, or 24

this formula works

=IF(A4="2 x 6 12 in centers",12,IF(A4="2 x 8 12 in centers",12,
IF(A4="2 x 10 12 in centers",12,IF(A4="2 x 12 12 in centers",12))))

but I have 8 more variables to go ending with ("2 x 12 24 in centers",24)
and the equation is going to be too long.

I tried using the special characters question mark "?" and asterik "*" but
got nowhere.

Any help out there? thank you - Chris, a swell estimator

What does the -- do before the MID?
Thanks,
Barb Reinhardt

"Teethless mama" wrote:

Try this:

=--MID(A4,FIND("in",A4)-3,2)


"swell estimator" wrote:

intent: use 12 variables to get 3 products - 12, or 16, or 24

this formula works

=IF(A4="2 x 6 12 in centers",12,IF(A4="2 x 8 12 in centers",12,
IF(A4="2 x 10 12 in centers",12,IF(A4="2 x 12 12 in centers",12))))

but I have 8 more variables to go ending with ("2 x 12 24 in centers",24)
and the equation is going to be too long.

I tried using the special characters question mark "?" and asterik "*" but
got nowhere.

Any help out there? thank you - Chris, a swell estimator

On Sep 8, 8:11*am, Barb Reinhardt
wrote:
What does the -- do before the MID?
Thanks,
Barb Reinhardt

"Teethless mama" wrote:
Try this:

=--MID(A4,FIND("in",A4)-3,2)

"swell estimator" wrote:

intent: use 12 variables to get 3 products - 12, or 16, or 24

this formula works

=IF(A4="2 *x 6 *12 in centers",12,IF(A4="2 *x 8 *12 in centers",12,
IF(A4="2 *x 10 12 in centers",12,IF(A4="2 *x 12 12 in centers",12))))

but I have 8 more variables to go ending with ("2 x 12 24 in centers",24)
and the equation is going to be too long.

I tried using the special characters question mark "?" and asterik "*" but
got nowhere.

Any help out there? *thank you - Chris, a swell estimator

OPs formula expressed a desire to return a number (12) not text ("12")
hence the -- operator.

Ken Johnson