Find a value based on criteria

Trying to calculate a score based on criteria that the user inputs.  On sheet 1 user enter Male or Female, their age, and number of PU they did and in Column D I need to calculate the score based on the data from sheet 1.  An example of a Male, 38, and 51 PU's would give a score of 9.4.  It would look under the Male chart, then the age bracket, then find the corresponding number of PU's they did in Column A and give the score.  Attached is an example.
count.xlsx
macone1976Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
Insert this formula below your input cells on Sheet 2:

=OFFSET(Sheet1!$B$3,MATCH(B3,Sheet1!$A$4:$A$104,0),IF(B1="M",0,5)+IF(B2>=70,4,INT(B2/10)-2))

Regards,
-Glenn
0
macone1976Author Commented:
Glenn

I tried to paste the formula but it is giving me a result of 0
0
Steven CarnahanNetwork ManagerCommented:
There seems to be a small flaw in the formula.  I put Female, 16 and 28.  It returns 9.5  when it should be 8.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

macone1976Author Commented:
Cant seem to get it to work.
0
Steven CarnahanNetwork ManagerCommented:
Here is picture using Glenn's formula in cell B5 on Sheet 2 with your default input.

Formula
0
macone1976Author Commented:
Aww i see, I need it the other way      Gender  Age PU, going across, not up and down like the image.
0
macone1976Author Commented:
How do I adjust to make it work the way I need it to.
0
Glenn RayExcel VBA DeveloperCommented:
I made it work in an example workbook.  Please take a look.

-Glenn
EE-Count.xlsx
0
Glenn RayExcel VBA DeveloperCommented:
Well, your original example had the metrics in a single column.  See the attached example workbook for both possible solutions (i.e., in a column and in a row).

Regards,
-Glenn
EE-Count.xlsx
0
Steven CarnahanNetwork ManagerCommented:
Glenn,

This is a nice job however I still get 9.5 when I use F/16/28 but using the chart on Sheet 1 that shows it should be 8.  Using the criteria given in the example (M/38/51) it comes back correctly.  I was just doing some spot checking when I found the one from my example.
0
Glenn RayExcel VBA DeveloperCommented:
Yep, there's an error if the age is less than 20.  Here's the corrected formula for the original sheet layout:

=OFFSET(Sheet1!$B$3,MATCH(B3,Sheet1!$A$4:$A$104,0),IF(B1="M",0,5)+MATCH(B2,{0,30,40,50,60},1)-1)

and for the new layout:
=OFFSET(Sheet1!$B$3,MATCH(D2,Sheet1!$A$4:$A$104,0),IF(B2="M",0,5)+MATCH(C2,{0,30,40,50,60},1)-1)

Updated workbook attached.

-Glenn
EE-Count.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
macone1976Author Commented:
Glenn,  Thank you so much for your help.  That worked perfectly.  I have just one more new question and my project will be finished, similiar to this question but different format.
0
Glenn RayExcel VBA DeveloperCommented:
Glad I could help.  I'll keep an eye out for your followup question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.