Formula Help (to many expresions)

[Smonczka]

Could one of you give me a hand with this...

I'm trying to put a formula in a spreadsheet that has too many
expressions in it. I understand there is a limit to the number of
equations that can be in a formula but there must be a way around the
cap.

Or maybe another way to write the formula?

What I am trying to say in the formula is that if...

If X is less than 09 then B1 = what's in cell C2
If X is less than 25 then B1 = what's in cell C3
If X is less than 51 then B1 = what's in cell C4

The expression I have written looks like this...

=IF(X<10,"N/A",IF(X<26,R10,IF(X<51,R11,IF(X<101,R12,IF(X<251,R13,IF(X<501,R14,IF(X<1001,R15,IF(X<2001,R16,IF(X<3001,R17,IF(X<5001,R18,IF(X<7501,R19,IF(X<10001,R20,IF(X<12501,R21,IF(X<15001,R22,IF(X<17501,R23,IF(X<20001,R24,IF(X<25001,R25,IF(X<30001,R26,IF(X<35001,R27,IF(X<40001,R28,IF(X<99999,R29,"N/A")))))))))))))))))))))

As you can see to many equations.

I was thinking of using a VLookup but is there a way to use a "<"
with a VLookup.


Thanks,
Steve

(e-mail address removed)

 

[JE McGimpsey]

One way:

=IF(OR(X<10,X>=100000),"N/A",INDEX(R10:R29,MATCH(X,
{10,25,50,100,250,500,1000,2000,3000,5000,7500,10000,12500,15000,
17500,20000,25000,30000,35000,40000},TRUE)))

or put the values inside the brackets into a range, rng, and use

MATCH(X,rng,TRUE)

 

[Ron Rosenfeld]

I was thinking of using a VLookup but is there a way to use a "<"
with a VLookup.

You need to use a lookup table.

There is no need to use the "<" operator since, if range_lookup is TRUE (the
default) your lookup array should be in sorted order and the result will be the
same as you are looking for.

From HELP:

"If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the
largest value that is less than or equal to lookup_value.

"If lookup_value is smaller than the smallest value in the first column of
table_array, VLOOKUP returns the #N/A error value."

For example (and I've changed your 'break points' a bit):

Set up a table in Q10:R30 as follows:

10 A
25 B
50 C
100 D
250 E
500 F
1000 G
2000 H
3000 I
5000 J
7500 K
10000 L
12500 M
15000 N
17500 O
20000 P
25000 Q
30000 R
35000 S
40000 T
100000 #N/A

In R30, there is a formula =NA()


Then use the formula:

=VLOOKUP(x,Q10:R30,2)

where 'x' is your lookup value.

The above will give #N/A if x is less than 10 or greater than or equal to
100000.



--ron

 

[Andy B]

Hi
You can use Vlookup to find the nearest value below the one you want. Your
list of options must be much bigger than you have posted - otherwise your
formula wouldn't be so long!
Have a look here for another option:
http://www.cpearson.com/excel/lookups.htm#ClosestMatch

Andy

 

[Guest]

First of all your formula is TRUE for all your conditions if it is less than
10, assuming you really meant if less than 10 do 1, if less than 26 BUT
greater than or equal to 10 do 2 and so on, then yes you can use VLOOKUP,
assume you have the results in C2 going down, in C2 put N/A, in B2 put 0, in
C3 put whatever you want etc, insert a new column B (if B is not already
empty) and in B3 put 10 in B4put 26 and so on, then just use


=VLOOKUP(X,B2:C20,2)


adapt to fit your table

you can also hard code your table like


=VLOOKUP(X,{0,#N/A;10,2;26,3;51,4;101,5;151,6;201,7},2)

If indeed you want N/A if less than 10 you could just skip that row
comlpetely and start with 10 in column B


Regards,

Peo Sjoblom

 

[Ken Wright]

Another option

=IF(OR(X<10,X>=99999),"N/A",INDIRECT("R"&MATCH(X,{0;26;51;101;251;501;1001;2
001;3001;5001;7501;10001;12501;15001;17501;20001;25001;30001;35001;40001;999
99})+9))

 

[Smonczka]

My god I didn't know there were so many ways to look at this. Thank
you all.

Steve

 

[Charlie Byers]

The Match() function takes an optional parameter (match_type in the help
text,) that specifies whether to find the nearest match less than, or more
than, the test value, when an exact match isn't found. That's the only
solution I've found to these kinds of problems, and it's usually just a
start; You can take the output of Match() into an
'indirect(address(match(...)))' kinda hairball to form a cell reference.
Do read the help text on the Match function, if you're not familiar with it.
The results can be counterintuitive.
HTH,
Charlie

 

[Smonczka]

Charlie, thanks for the info on Match(). It's a function I have not
used before now. The problem with (and benifit of) Excel is that there
are so many formulas to choose from it's hard figure out which one to
use let alone know all of them.

Anyway thanks for the kind help in pointing me in the right direction.

Steve