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
count.xlsx
ASKER
Glenn
I tried to paste the formula but it is giving me a result of 0
I tried to paste the formula but it is giving me a result of 0
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.
ASKER
Cant seem to get it to work.
Here is picture using Glenn's formula in cell B5 on Sheet 2 with your default input.
ASKER
Aww i see, I need it the other way Gender Age PU, going across, not up and down like the image.
ASKER
How do I adjust to make it work the way I need it to.
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
Regards,
-Glenn
EE-Count.xlsx
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Glad I could help. I'll keep an eye out for your followup question.
=OFFSET(Sheet1!$B$3,MATCH(
Regards,
-Glenn