Classify data values

[s82938]

Hi,
I'm new on this forum and not a Excel expert... so I'd like your help to solve the following problem...

I have a list of years, e.g.:

1420, 1460, 1500, 1500, 1500, 1580, 1600, 1600, 1670, ...

and I have a list of year ranges, e.g:

1400/1500 (1), 1500/1600 (2), 1600/1700 (3), ...

I'd like automatically to assign each year to his range of belonging using some formula. For the example above the result should be:

(1), (1), (1), (1), (1), (2), (2), (2), (3)

Maybe with the attached file is easier to understand the example...

Thanks in advance...

[martindwilson]

what do you want 1500 to be? is it 1401-1500 1501-1600 or do you want it in both?
perhaps like this

[arthurbr]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

[s82938]

what do you want 1500 to be? is it 1401-1500 1501-1600 or do you want it in both?
perhaps like this

I'd like 1500 to be in both (1400-1500 and 1500-1600)
so 1500 should be classified as range 1 and also range 2

I added the field "Value" on the attached file.
My final goal is to calculate the average of "Value" data that belong to the same year range. But, in the example, the values B4, B5, B6 have to belong to range 1 and 2, so they have to be considered in two average calculation... (average of values in range 1 and average of values in range 2)
The same B8 and B9 belong to range 2 and 3...

... view attached file!

Thanks...

[martindwilson]

cant do anything more until you have complied with mod's request

[royUK]

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc

[s82938]

I really apologize to the moderator!
I hope my post complies all forum rules now...
thanks....