Solved

Posted on 2011-10-26

Hi,

Have a tough one that I think I'm going to need help with! How can a set of ranges (arbitrary alphanumeric ranges within a range of cells) which have a specific column identifier be found based on two user variable inputs?

To make that a bit clearer (hopefully!), there needs to be manual input of an age range (for example '30-39', preferably from a drop-down list) and a numeric value between upper and lower limits (for example, 1000-2000); let's say these are in Column A and B respectively.

The A value has 100 different alphanumeric 'ranges' assigned, which are divisions of the range withn the upper and lower limits of the B value, e.g. 1000-1009, 1010-1019, 1020-1029, etc. Each of these ranges has an associated 'score', from 1 to 100. Each A value will have different upper and lower limits (e.g. 20-29 might be 1500-3000), but always the same number of divisions (100, and therefore the same 1 to 100 'scoring').

The formula I need to create will look at the A and B values and based on them, reveal the 'score' (between 1 and 100) for that combination of age range (A) and numeric value (B), based on which of the 100 ranged divisions the numeric B vlaue falls into.

Confused? Yeah, me too. :)

I thought this might be possible via a combination of HLOOKUPS and VLOOKUPS, but having gone through it in my head (not really tried it in Excel yet), I'm pretty sure it will need something else because I can't see how to pluck out the 'score' (which would be just another cell value in a row/column) based on the chosen age range (A) and numeric value (B).

Any ideas?!?!

Have a tough one that I think I'm going to need help with! How can a set of ranges (arbitrary alphanumeric ranges within a range of cells) which have a specific column identifier be found based on two user variable inputs?

To make that a bit clearer (hopefully!), there needs to be manual input of an age range (for example '30-39', preferably from a drop-down list) and a numeric value between upper and lower limits (for example, 1000-2000); let's say these are in Column A and B respectively.

The A value has 100 different alphanumeric 'ranges' assigned, which are divisions of the range withn the upper and lower limits of the B value, e.g. 1000-1009, 1010-1019, 1020-1029, etc. Each of these ranges has an associated 'score', from 1 to 100. Each A value will have different upper and lower limits (e.g. 20-29 might be 1500-3000), but always the same number of divisions (100, and therefore the same 1 to 100 'scoring').

The formula I need to create will look at the A and B values and based on them, reveal the 'score' (between 1 and 100) for that combination of age range (A) and numeric value (B), based on which of the 100 ranged divisions the numeric B vlaue falls into.

Confused? Yeah, me too. :)

I thought this might be possible via a combination of HLOOKUPS and VLOOKUPS, but having gone through it in my head (not really tried it in Excel yet), I'm pretty sure it will need something else because I can't see how to pluck out the 'score' (which would be just another cell value in a row/column) based on the chosen age range (A) and numeric value (B).

Any ideas?!?!

10 Comments

I'll see if I can mess with the data tonight.....thanks for the input on how to arrange it though, I'd not been too sure at all how best to do that. The format is pretty raw at the moment, so before I post anything I'll tidy it up (I'll also need permission to release it).

However, the range division is defined within the cell as just as text at the moment (i.e. written as '1010-1019'). How can '1017' be deemed to fall into that range by the formula and then the correctly score assinged? A format change seems imminent, but to what?

1000, 1010, 1020, 1030, 1040

and your lookup value 1017 is in H1 then this formula

=MATCH(H1,B1:F1,1)

will return 2 (because 1017 matches with the greatest number less than or equal to 1017 in the lookup range, in this case 1010, that's in the second cell of B1:F1 so 2 is returned).

Note the 1 as the 3rd argument of MATCH function, changed from zero in the example I linked to. Zero gives an exact match only.

You can use either type of MATCH function within the INDEX/MATCH/MATCH setup

regards, barry

@mark

If you want to convert the textual range '1010-1019' (in $B8) to numbers you can use:-

=VALUE(MID($B8,1,FIND("-",

=VALUE(RIGHT($B8,FIND("-",

This will give you the range you need for the MATCH function in one column as (I think) Barry meant

Yes, I was suggesting that only numbers should be used, i.e. the lower boundary of each range - that would make the formulas simpler. If the range is required to be shown that could be in another column.

Mark,

I re-read your question.

Does it work like this?

An age range is selected (from dropdown) in A2 and then that age-range will have a numeric range associated with it, e.g. 1000-2000 so in B2 a number between those is selected.

You then assign a score (1 to 100) based on where that number falls in the range.

If the range is

column A has a dropdown which only allows you to pick an age range shown in F2:F8. Column B also has validation so that you can't enter a value that isn't within the relevant range, then C2 uses this formula to get the score

=IF(COUNTA(A2,B2)=2,MIN(IN

I cheated a little because with a range like 1000-2000 you score 1 in the range 1000-1009, 2 in 1010-1019 etc....but that would make 2000 score 101 so I had to restrict that with MIN (the last range, therefore, for 100 score, would be 1990-2000 inclusive. Not sure if that's acceptable?

If the divisions within the range

For that one I set up validation in a similar way and used this formula in C2

=IF(COUNTA(A2,B2)=2,MATCH(

In that version the ranges can be anything you want them to be, equal or unequal

Maybe neither setup gives you exactly what you need but one of them, at least, might be a good start point

regards, barry

27416410.xls

Barry - I messed around a little with that file, adding in the data I'd been given. I think it is doing what is required, but I'm just waiting for some feedback from the end user to confirm. I'd only been given a very quick brief, so there may some other things (but the bulk of what is needed seems to be there IMO).

By clicking you are agreeing to Experts Exchange's Terms of Use.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**18** Experts available now in Live!