Limit on Logic

[Nash13]

I am trying to build a logic statement with 11 arguments. We are using Excel
2003 which I am assuming has a limit on 7 such arguments. I've attached my
formula below, how can I bypass these limits?

Thank you

=IF(D2>=10,"250",IF(D2>=9,"225",IF(D2>=8,"200",IF(D2>=7,"175",IF(D2>=6,"150",IF(D2>=5,"125",IF(D2>=4,"100",IF(D2>=3,"75",IF(D2>=2,"50",IF(D2>=1,"25",IF(D2>=0,"0","N/A"))))))))))

 

[Ron Coderre]

If D2 will only contain numbers

Try this:
=IF(D2<1,"N/A",MIN(INT(D2)*25,225))

If it may contain text or numbers:
=IF(N(D2)<1,"N/A",MIN(INT(D2)*25,225))

Does that help?
***********
Regards,
Ron

XL2003, WinXP

 

[Harlan Grove]

I am trying to build a logic statement with 11 arguments. We are
using Excel 2003 which I am assuming has a limit on 7 such arguments.
I've attached my formula below, how can I bypass these limits? ....
=IF(D2>=10,"250",IF(D2>=9,"225",IF(D2>=8,"200",IF(D2>=7,"175",IF(D2>=6,"150",IF(D2>=5,"125",IF(D2>=4,"100",IF(D2>=3,"75",IF(D2>=2,"50",IF(D2>=1,"25",IF(D2>=0,"0","N/A"))))))))))

Logically, you should use a lookup for this.

=LOOKUP(D2,{-1E307,"N/A";0,"0";1,"25";2,"50";3,"75";4,"100";5,"125";
6,"150";7,"175";8,"200";9,"225";10,"250"})

or, since there's a pattern here,

=TEXT(MIN(10,ROUNDUP(D2,0))*25,"0;""N/A""")

 

[Max]

Another option is to use vlookup
=IF(ISBLANK(D2),"-",VLOOKUP(D2,{0,0;1,25;2,50;3,75;4,100;5,125;6,150;7,175;8,200;9,225;10,250},2))

Note that ".." should not be wrapped around numbers in formulas as this will
result in text numbers rather than real numbers.

 

[Harlan Grove]

=TEXT(MIN(10,ROUNDUP(D2,0))*25,"0;""N/A""")

Screwed that up. Make that

=TEXT(MIN(10,INT(D2))*25,"0;""N/A""")

 

[Harlan Grove]

Note that ".." should not be wrapped around numbers in formulas as
this will result in text numbers rather than real numbers.

....

And you read the OP's mind to determine he wants actual numbers rather
than text? Or are you just being patronizing, assuming that the OP is
as ignorant of the distinction between numeric text strings and
numeric values as s/he apparently is of lookup functions?

 

[Max]

No need to pour on the acidics.
It was just an observation point to the OP
based on the OP's formula that was posted

 

[Harlan Grove]

No need to pour on the acidics.
It was just an observation point to the OP
based on the OP's formula that was posted

....

Not acidics, reasoned observation.

"Note that ".." should not be wrapped around numbers in formulas as
this will result in text numbers rather than real numbers."

can only be interpreted as patronizing.

"Note: wrapping double quotes around numbers makes them text, so the
formula's result would effectively be treated as numeric zero by most
numeric formulas referring to it."

is neutral. Your use of 'should' makes all the difference. Or do you
have a tenuous grip on English usage?

 

[Max]

Not acidics, reasoned observation.

Undoubtedly, but expressed in an acidic manner

"Note that ".." should not be wrapped around numbers in formulas as
this will result in text numbers rather than real numbers."
can only be interpreted as patronizing.

By you, you mean? To be honest, I wasn't aware of that possible construence.

.. Your use of 'should' makes all the difference.

To you, you mean. I'm sorry if using "should" somehow stroked you the wrong
way up here.

"Note: wrapping double quotes around numbers makes them text, so the
formula's result would effectively be treated as numeric zero by most
numeric formulas referring to it."
is neutral.

An alternative way to express it, Fair enough.
That it is neutral or not is debatable.

Or do you have a tenuous grip on English usage?

Better than tenuous, I'd say. But I choose to express it in pleasant
English.