Lookup range of numbers in one column and average corresponding data from next column

Hello,

I'm new at Excel Macros and I'm a quick study but I need to get a job done for work and I can't find a simple answer online.

I have to average out the percentage complete rate for a building of 4 floors, for each floor. Each floor has room numbers that correspond to the floor, ie: First Floor are all 100's, Second are all 200's, etc. I have one column (Column A) with room numbers not in numerical order and a few columns down I have the percentage complete for that one room based on other data that I already have Excel pulling from other columns.

I need to make a new worksheet that looks for specific ranges in column A (say lookup numbers 14 to 22) and then pull the corresponding data from Column O and average the total out into a cell in the new worksheet. I'm assuming I need a Macro because I can't specify cell by cell where each of these numbers will be and then pull the data because I need to apply this formula to many sheets and the numbers vary in row order by sheet. They will always be in Column A but not always in the same row.

I hope I'm making sense but if possible I just need coding or a formulaRange_Lookup_Average_Sample.xlsx I can copy and paste and then just tweak to my needs. I'm attaching an Excel file sample of what I need. There are two sheets, one has the data I need to pull and the other table I need to put it in.

If you have suggestions as to how to make the table more awesome I would be happy to hear that too! Thanks SO MUCH for your time and patience!!! Oh and I'm using Excel 2007

Sorry I got a lot going thru my head lol

Ok so I need the range lookup to look thru Column A for different number ranges. For example on the "Data" sheet one range to look up is numbers 14-22 for Basement East. Another is 25-41 for Basement Center. I need it to find the numbers in this range on Column A and then pull the data from the same row the number is in but pull data from from Column O and place the Average result in Cell B2 for Basement East and B3 for Basement Center on the Sheet titled "Table with Averages per Floor".

I don't need the numbers in Column A, I need the Completion Rate for those numbers Averaged out into one total amount. We need to find out the completion rate for each floor. Each floor is comprised of rooms and each number indicates what floor that room is on. On Column O on the "Data" sheet I have a Completion Rate for each room. I need to combine the completion rate for each room from each floor and place that total into a cell on the "Table with Averages per Floor" sheet.

Did I explain that correctly? Sorry please let me know if you need more info and thanks so much for replying so quickly!

Attached is an updated version of the file I added the Room and %of Room Complete titles to the columns so you can see what I mean. Thanks!

Hmm ok on the first point it you're probably very right and I am honestly not good at math so I'll look into that. I basically just want to see how far along the whole floor is based on how far along each room in that floor is completed. Any suggestions on how to do that? Thx for your help btw

About the second point here is what I did: there are 5 steps that the walls in these rooms need to go thru to be completed. They wanted a check off list so I did 5 columns where they could check off each step then I found a formula online where I could use the amount of check off's and multiply them by .2 to equal 100% complete. Each checked off cell equals 20 and once all checked off the total is 100 and I just made it a percent. They just need to see how far along the work is and there is no numerical data to input just a check off sheet.

I forgot the 3rd question lol well I gave some samples of ranges. Rooms 14-22 are in Basement East, 25-41 are in Basement Center. Basement East and Center are the floors that need a total completion rate. I would have the range be different for each floor since the room numbers are different and I would have that total be entered in the cell under each floor on the second sheet. There are many more room numbers but once I get the formula I should be able to just change the range numbers.

Ok so I put that in the cell in the Second sheet where I need the total completion for that floor and it gives me 93% complete based on that formula. The 3 rooms that are in that range you put are 100%, 60% and 40% complete. But I really only need the x's from $E$2:$N$28 (sorry I should've specified that) so I changed that and it gives me 67% complete. I tested it again by changing the range from 129 to 132 which would only pick up 2 rooms in that range and they are 100% and 40% complete and the result of the formula for both is 70% complete. The sad part is that I don't really know if the math is correct but it seems right and judging from your incredibly quick reply I'm gonna trust that you know what you're talking about. So THANK YOU for being a genius. I'll mark this as Solved. Thanks again!

Did the math.... its right!!!! U ARE A GENIUS thanks again