I need a better way or better function than the 'IF' function

[havocdragon]

Hello all! So here is what I am having troubles with; I have a situation
similar to grading a score , for expamle '=IF (A1>89,"A",), but the problem
is, I need to do this for 40 arguements instead of the maximum 7 (41 if you
include 0). I was wondering if there was another way, or another similar
function I can use to do this?

 

[Chip Pearson]

You should use the VLOOKUP function.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
news:[email protected]...

 

[havocdragon]

Ok, I have been looking through the VLOOKUP function, and im not sure how
exactly I would do that. ill give you an example as best I can for what im
doing


A1 B2
1st =between 0 and 999
2nd =between 1000 and 2999
3rd =between 3000 and 5999
4th =betweem 6000 and 9999
5th =between 10000 and 14999
6th =between 15000 and 20999
7th =between 21000 and 27999
8th =between 28000 and 35999
9th =between 36000 and 44999
10th =between 45000 and 54999

I dont actually have the =between x and x in the cell, I actually need to
know how to do that to =). Also how would I make vlookup find the range lets
say 500, should find that its in the cell 'between 0 and 999' it should
output '1st' in the formula cell. I hope that makes sense =). If you need
more info let me know =)

 

[RagDyeR]

Actually, for the way your data is set up, Vlookup is not appropriate, since
the lookup value is not in the left most column.

You mention 40 arguments (rows), so you can just expand on what I did to
follow your example.

A1 to A10 = as you posted,

B1 to B10
0
1000
3000
6000
10000
15000
21000
28000
36000
45000


The number to lookup is entered in G1.

And try this formula:

=INDEX(A1:A10,MATCH(G1,B1:B10,1))

With this formula, *any* number larger then 45,000 will return 10th.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Ok, I have been looking through the VLOOKUP function, and im not sure how
exactly I would do that. ill give you an example as best I can for what im
doing


A1 B2
1st =between 0 and 999
2nd =between 1000 and 2999
3rd =between 3000 and 5999
4th =betweem 6000 and 9999
5th =between 10000 and 14999
6th =between 15000 and 20999
7th =between 21000 and 27999
8th =between 28000 and 35999
9th =between 36000 and 44999
10th =between 45000 and 54999

I dont actually have the =between x and x in the cell, I actually need to
know how to do that to =). Also how would I make vlookup find the range lets
say 500, should find that its in the cell 'between 0 and 999' it should
output '1st' in the formula cell. I hope that makes sense =). If you need
more info let me know =)

 

[havocdragon]

RagDyeR, thanks much!! That works like a charm. Although I have one question
for you on this formula; I have figured out what all the arguments are for,
and I know what the functions Index and Match do, but what I cant figure out
is what the '1' at the very end of your formula does.

 

[Biff]

That determins whether the MATCH function looks for an
exact match or the closest match. 1 is for closest match
and 0 would be for an exact match.

Biff

 

[RagDyeR]

Actually, I could have omitted it, since it's the parameter that's
automatically assumed for this function.
It's not a bad habit to always include it though, as a reminder of what is
supposed to happen.

A "1" as the third argument for Match() will return the largest value that
is less then or equal to the look-up value.
However, the look-up list (array) *must* be in ascending order.
As you can see, this fits your scenario exactly.

A "-1" as the third argument for Match() will return the smallest value that
is greater then or equal to the look-up value.
However, the look-up list (array) *must* be in descending order.

Finally, a "0" as the third argument will return the *first* exact match to
the look-up value.
In this case, the look-up array does *not* have to be in any order.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


RagDyeR, thanks much!! That works like a charm. Although I have one question
for you on this formula; I have figured out what all the arguments are for,
and I know what the functions Index and Match do, but what I cant figure out
is what the '1' at the very end of your formula does.

 

[Dana DeLouis]

Don't know if this will help. Are you following a known sequence in your
table?

1000*n*(n + 1)/2

as n goes from 0 to 9? (Perhaps as n goes to 41?)

If so, perhaps as an alternative:

=ROUNDDOWN((-25 + SQRT(5)*SQRT(125 + A1))/ 50,0)+1

For example:
999.99 -> 1
1000 -> 2
45000 ->10

HTH
Dana DeLouis

 

[Dana DeLouis]

Oops. Slightly better...

=INT((SQRT(5)*SQRT(125+A1)-25)/50)+1

Dana DeLouis

Don't know if this will help. Are you following a known sequence in your
table?

1000*n*(n + 1)/2

as n goes from 0 to 9? (Perhaps as n goes to 41?)

If so, perhaps as an alternative:

=ROUNDDOWN((-25 + SQRT(5)*SQRT(125 + A1))/ 50,0)+1

For example:
999.99 -> 1
1000 -> 2
45000 ->10

HTH
Dana DeLouis

<snip>

 

[havocdragon]

Dana- thanks as well!!! But I must say, I am pretty good at math, and
although your formula works great, and cant figure out why it works great =).
If its all possible can you give me the run down on your formula? I would
like to better understand it so I can become creatively better at functions =)

 

[havocdragon]

Actually nevermind I got it. Just learned how to use the evaluate formula
button =)