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",I F(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


"Nash13" wrote:

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",I F(D2=0,"0","N/A"))))))))))

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)))
--
David Biddulph

"Nash13" 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",I F(D2=0,"0","N/A"))))))))))

Nash13 wrote...
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""")

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

Screwed that up. Make that

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

=MIN(D2*25,250)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Nash13"
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",I F(D2=0,"0","N/A"))))))))))

Tuesday Eve

Using this formula:

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

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

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

--
Biff
Microsoft Excel MVP


"J" wrote in message
...
Tuesday Eve

Using this formula:

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

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

Short and to the point. Thanks much.

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

"Jim Cone" wrote:


=MIN(D2*25,250)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Nash13"
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",I F(D2=0,"0","N/A"))))))))))

Short and to the point. Thanks much.

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

"Jim Cone" wrote:


=MIN(D2*25,250)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Nash13"
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",I F(D2=0,"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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nash13" wrote:
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",I F(D2=0,"0","N/A"))))))))))

Max wrote...
....
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?

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Harlan Grove" wrote in message
ups.com...
Max wrote...
...
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" wrote...
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?

"Harlan Grove" wrote
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---