Limit on Logic

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"))))))))))
 
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
 
In general in this sort of logic you'd be better off with a lookup function
(though CHOOSE is probably the best option in this particular case as you're
stepping by 1 each time on your inputs, but as your output increments are
equal see later).
And do you really want to return a text string as the output? If you want
numbers, get rid of the quotes round 225, 200, etc.
And do you want to return the text string "N/A" or return the #N/A error
value?

One option might be =IF(D2<0,NA(),25*INT(MIN(D2,10)))
 
=MIN(D2*25,250)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Nash13" <[email protected]>
wrote in message
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"))))))))))
 
Nash13 said:
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"))))))))))
Click to expand...

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""")
 
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.
 
Max said:
Note that ".." should not be wrapped around numbers in formulas as
this will result in text numbers rather than real numbers.
Click to expand...
....

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?
 
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

---
 
Max said:
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
Click to expand...
....

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?
 
Tuesday Eve

Using this formula:

=IF(J8="@",J8,IF(J8=".",J8,VLOOKUP(J8,table1,2,FALSE)))

J8 has a single alpha character

table1 is two columns
column 1 is letters a-z, and A-Z
column 2 is the corrosponding ACII code

The formula is NOT returning an exact match.

How do I make if so?

I have tried sorting column 1 different ways.

Help me Rhonda, Help, Help me Rhonda

J
 
Harlan Grove said:
Not acidics, reasoned observation.
Click to expand...
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.
Click to expand...
By you, you mean? To be honest, I wasn't aware of that possible construence.
.. Your use of 'should' makes all the difference.
Click to expand...
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.
Click to expand...
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?
Click to expand...
Better than tenuous, I'd say. But I choose to express it in pleasant
English.

---
 
The formula is NOT returning an exact match.

That's because to VLOOKUP "a" and "A" are the same, it's not case sensitive.
So, whichever is listed in the table first is the one VLOOKUP will "find".
This is where "exact match" doesn't really mean exact match!

Assuming that J8 will contain only the letters a-z, A-Z, "@" or "dot", try
this (no lookup table needed):

=IF(OR(J8={"@","."}),J8,CODE(J8))
 
Short and to the point. Thanks much.

Thanks to everyone else who posted as well, it is appreciated.
 
Back
Top