Reading a value from a table based on less than or equal to

**wizzard21** · 07-19-2014, 09:53 AM

Hello all,

I'm new to the forums and I feel helpless in getting excel to do something as I'm not excel expert.

So I have a table of about 50 groups and 10 ranks. For the purposes of this question and making it easier on folks, I just used 5 groups and 8 ranks and figure I can just figure the additional groups and ranks once I get the method down.

Here is my table which I have in another sheet:
\1

The first columns are the ranks (1-8), the first rows are the groups (1-5) and the rest are scores. For example, Group 1 can have a score between 0 - 14 and Group 2 can have a score between 0 - 29 etc. etc.

In another sheet I have the Group number in one cell, what I need to do is get the rank based on the group number and their respective score.

How it works is that if Group 1 score is less than or equal to 0, they are ranked as "1". If Group 1 score is less than or equal to 2 they are ranked as "2". So on and so forth for the additional groups, scores and ranks.

Anyway to easily to this? I've done VLOOKUPS but not with less than or equal to.

Since I need to do assign ranks for 50 groups, any shortcuts would be appreciated!

**newdoverman** · 07-19-2014, 10:23 AM

This is not clear at all.

Each group that you show has 8 scores. What score is used to calculate the rank?

Can you upload a workbook instead of the picture that shows what you want on the second worksheet? Accurate figures would be necessary in order to try and create a formula to calculate the figures for you.

**wizzard21** · 07-19-2014, 10:45 AM

Hi newdoverman,

Sorry for not being clear, but I appreciate your patience.

I've attached a sample workbook. The tab labeled "master sheet" shows the group numbers and their respective scores.

The rank sheet is how it determines the rank, based on the scores and group. So the region shaded purple are the groups (1-5) and the region shaded light blue are the ranks (1-8), everything else are scores.

Hope this makes better sense. Thank you again for your help!

**oeldere** · 07-19-2014, 10:51 AM

@wizzar21

you can add the excel file on this forum.

not all forummembers want to open (or download) files on a external place.

**wizzard21** · 07-19-2014, 10:54 AM

Thanks, Oeidere. I must have fixed it as soon as you typed up that reply.

**Vikas_Gautam** · 07-19-2014, 11:03 AM

Try this..
Simple lookup and named range has done this...
a definite solution..
check the attachment..

Don't forget to click *

**newdoverman** · 07-19-2014, 11:06 AM

It seems that you have different rankings for the different groups. What ranks are expected in column D for the various groups. You give an example for group 1 but no idea how the rankings go for the other groups.

**wizzard21** · 07-19-2014, 11:26 AM

Wow Vikas that seems to work beautifully. Since I've never done a lookup like this, can you provide any explanations on how the formulas so that I can replicate? I would have expected to see some less than signs, but seems like you got it to work without needing that.

Newdoverman, you are correct there are different scores for each groups. Basically each group needs to be at the required score to be within that rank. For example, Group 5 needs at least a 20 (cell D7) to be included in Rank 3, if they get a score of 19 they go to Rank 2 since this is less than 20.

Does that clear up the confusion?

**Vikas_Gautam** · 07-19-2014, 01:08 PM

don't credit me for this..
give the credit to this forum...
who has provided me all the knowledge..

Explanation..

first I named each scores ranges to Rank1 , Rank2 , Rank3 and so on...
then I used lookup with the veritcal arrays to find the score range matching with the group...

then I used indirect function to change Rank1 text to Rank1 Named Range..

then I lookup the scores in the Rank1 Score range and result the corresponding rank no.. by working horizontally this time....

I hope you can understand my explanation..

For more clarification.. Go to formula Auditing-----> Evaluate formula...

Don't forget to click *

**newdoverman** · 07-19-2014, 03:57 PM

This is based on a solution by daddylonglegs http://www.excelforum.com/excel-gene...mn-header.html

Enter this formula in Master Sheet C2 and copy down:

Formula:

Please Login or Register  to view this content.

**newdoverman** · 07-19-2014, 04:16 PM

This is the formula expanded to include 10 RANKS and 65+ rows.

Formula:

Please Login or Register  to view this content.

**wizzard21** · 07-20-2014, 08:48 AM

mods please delete

**wizzard21** · 07-20-2014, 08:50 AM

Originally Posted by Vikas_Gautam

I hope you can understand my explanation..

For more clarification.. Go to formula Auditing-----> Evaluate formula...

Don't forget to click *

Well thank you and the forums so much! Seems to make sense and I think I can easily modify this.

**wizzard21** · 07-20-2014, 08:54 AM

Originally Posted by newdoverman

This is the formula expanded to include 10 RANKS and 65+ rows.

Formula:

Please Login or Register  to view this content.

Newdoverman these work beautifully! Through conversation someone else mentioned the index function to me, so it is nice to see it in action!

Thank you again for your patience, it is surely appreciated!

Quick question, I have another issue that I need help with. Should I post a new thread?

**newdoverman** · 07-20-2014, 08:58 AM

If it is directly related to this question post it here otherwise, create a new thread.

**wizzard21** · 07-21-2014, 09:48 AM

Originally Posted by newdoverman

If it is directly related to this question post it here otherwise, create a new thread.

Turns out this isn't working and I'm for sure that I'm doing something wrong. So as it turns out I have 9 Ranks and 26 Rows. I tried to modify your formula to this:

Formula:

Please Login or Register  to view this content.

But excel is asking me to update the values and then I get an #N/A error. Does it matter that in my master sheet the group cell appears at IT3 (instead of a2 as it was in the example) and my score cell appears at DF3 (instead of b2 as it was in the example)?

Thank you in advance for any help that you can provide.

**wizzard21** · 07-21-2014, 10:31 AM

Figured it out, I had an extra space in my reference sheet. Doh

**wizzard21** · 07-26-2014, 09:48 PM

Hello newdoverman and Vikas_Gautam and anyone else who can help,

So it turns out that as soon as I thought that I had it finished up, the formulas suggested which were indexing and indirect look ups aren't working how I need them to. I'm almost certain this is my fault through my explanation.

What both of these formulas seem to be doing is looking up the score and putting them at the ranks if they are at that score for that rank. For example, a case that is in group 1 and has a score of 2 should be ranked as "2" however, if they have a score of 3, they should be ranked as a "3" since they exceed the score for rank 2 but are less than or equal to the score for the start of Rank 3, which is a four.

Just to give another example, if there is a case in group 5 that has a score of 11-20, they should be ranked a 3 since they exceed the rank 2 score but are less than or equal to the score for rank 3 (20). If there is a case in group 5 that has a score between 21 - 30, they should be ranked as a 4.

I've attached the example sheet again for both solutions suggested.

Sorry for the long winded response. But I appreciate any help that anyone can provide.

**newdoverman** · 07-27-2014, 09:12 AM

This will work if the order of the Ranks is reversed. It is the same table but in reverse order.

**Vikas_Gautam** · 07-27-2014, 09:38 AM

yeah..

I think Dover's above solution... is right and simple...

Good one Dover..!

Regards,
Vikas Gautam

**newdoverman** · 07-27-2014, 09:44 AM

@Vikas_Gautam

This had me stumped until I re-read the match function. (I thought that I had this down cold) The key was to have the values in descending order...who would have thought that?

**wizzard21** · 07-27-2014, 12:07 PM

Thanks once again, newdoverman.

I added in another rank, to include a total of 9 and also added in 5 more groups to have 10 groups total.

I modified your formula to account for the additional rank and additional groups.

For some reason it won't let me paste my formula here, so I've attached another workbook.

Did I get it right? For this formula to the values have to go in descending order from left to right, correct?

**newdoverman** · 07-27-2014, 12:19 PM

I took a quick (I'm in a bit of a hurry) look and it appears to be right.

Reading a value from a table based on less than or equal to

LinkBack

Thread Tools

Rate This Thread

Display

Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Re: Reading a value from a table based on less than or equal to

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Reading a table from Word

Reading a Table Cell with TDelement Problem

Reading last row of growing table

Reading info from Table

[SOLVED] pivot table not reading date formats

Bookmarks

Bookmarks

Posting Permissions