Certain ranges determining a value

[jft1469]

Sorry for the duplicate post.

Good day.

I have have been pounding my head against the wall trying to determine a way to have certain ranges recognized and a value created from the range. An example for what I am trying to acheive would be a formula to create the results in column C:

Column A Column C
Volume Rating
22,222,222 4
26,666,666 5
12,000,000 3
8,000,000 2
4,000,000 1

The Key I am using for the rating is:
25,000,000 < = 5
14,000,000-24,999,999 =4
10,000,000-13,999,999 = 3
6,000,000 - 9,999,999 = 2
5,999,999 > = 1

So anything within a certain range would have a rating created by the formula in column C. Any help would be GREATLY APPRECIATED!!

[GeneralDisarray]

this is one of the times i've found the Vlookup with the "partial match" option is useful (actually the only time i can think of).

See the attached:

Basically you need to have a list of "ceiling" or cut-off values and rankings....must be ordered least to greatest.

Then you use VLOOKUP() with the partial match:

=VLOOKUP(testValueAddress,TableReference,column,TRUE)

Viola!

Have a look a the E column for the actual formula i used.

[tigeravatar]

Alternate formula:
=IF(A2<6000000,1,MATCH(A2,{6000000,10000000,14000000,25000000},1)+1)

[GeneralDisarray]

So, Match can also do partial...but of course...that is cool

[GeneralDisarray]

Guess if we wanted to be as cool as NBVC we have to nest that match with an index ...both directions:

=INDEX($A$2:$B$6,MATCH(D3,INDEX($A$2:$B$6,,1),1),2)

----

[tigeravatar]

@GeneralDisarray, Since OP only needs numbers 1 though 5, there's no need for a table using the match formula I provided. If the OP needed something other than numbers, you could use the Index/Match combo as you've suggested, but there would still be no need for a table, you would simply include the desired outcomes in the formula. For example, if, instead of 1,2,3,4,5, we wanted the results to be A,B,C,D,E, we could use the following formula:
=IF(A2<6000000,"A",INDEX({"A","B","C","D","E"},MATCH(A2,{6000000,10000000,14000000,25000000},1)+1))

See attached for how it works.

That's not to say that NBVC or DaddyLongLegs or ConnexionLost and many others don't have better/shorter/more efficient formulas, but its never a bad thing to practice and learn new tools.

[GeneralDisarray]

i see that it works...it's a nice formula...my last contribution was meant to be light-hearted not argumentative

my head was still spinning from another solution provided by NBVC (on http://www.excelforum.com/excel-gene...of-lotids.html)

[tigeravatar]

ah, sorry, I often seem to mistake light-heartedness/humor and read it literally >_<. Sometimes I feel like the following:
=IF(PostContainsHumor=TRUE,IF(Viewer="tigeravatar","Miss humor"&"Take literally","Enjoy humor"),"Enjoy humor")

(Pardon my attempt at excel humor, it just doesn't seem to come naturally, lol)

[jft1469]

Thank you all very much for your help!!!! Worked like a peach!! You all rock!