Select value in table based on Gender and Weight

Welcome to the forum!

Images are not much use: please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks. What is the logic for choosing the correct coefficient, please?

This uses a little used optional 4th argument of INDEX called [area_num]. It works well with multiple tables of identical type and layout. The thing to remember is that the first argument of INDEX ... multiple areas ... must be comma separated and enclosed in parentheses.

The formula I used is

Formula:

Please Login or Register  to view this content.

I believe my results are correct, but I don't come up with the same Female coefficients as post 1 indicates. All the Male coefficients agree however.

Gender	Weight	Score 1	Score 2	Score 3	Score 4	Score 5		Total	End Score
									= total * Coeffiecient
Male	80.7	50	51	60	55	62		122	158.6
Male	75.6	55	52	58	60	61		119	166.6
Female	60	40	48	48	50	0		98	159.74
Male	95.1	60	65	66	68	0		134	160.8
Female	66.8	45	0	50	52	53		103	156.56

@Dave - thank you for this! I was looking at the area argument of the INDEX function for the first time only the other day and wondering when/how it might be used. This makes it clear - a really useful tip!

@ Dave, thank's alot, gonna try this out. As for why the Female End Scores do not match the answer is the folllowing;
159.74/98= 1.63 Which is the coeffiecient of a class lower(up to 55 instead of up to 65) the same is true for
156.56/103= 1.52 Again a class lower then what it should be.

Anyway's, time for lunch here and then i go and play around whit your formula a bit. Hope i can disect it correctly

***EDIT***
So I just tried copy/pasting your formula but i trows me an error;
There's a problem whit this formula

Not trying to type a formula?
yada yada yada

When i press "ok" it highlights the H4, INDEX part of the formula but i cant figure out what's wrong whit it.

Please note I am using excel 2016, where as you are using excel 2013.
Maybe they require different formatting of formula's?

Could you please attach the workbook whit working code to your post so i can open it in 2016 and see if that works?

***EDIT***
Turns out it is because I'm in a different region I have to use ; as a argument separator instead of ,
Changed it all over and it works, except for the wrong weight class selection for female's.
Noticed you said about comparing identical tables so i added a line to the female reference table,
so they both have the same number of rows, does not change anything.

Also, if I move the values in the table up or down whithin the selected array the end product stay the same.
So it seems that the selection criteria for the weightclass for females is different then for males,
No idea how to fix that though.

Actually, could it be to do whit that fact that the weight classes for males are 50, 60, 70 etc and
for females they are 55, 65, 75 etc?
Getting a feeling excel is using 50, 60, 70 etc for females as well.

***EDIT***
See attachment, added column S to easily identify the coeffiecient used by excel and as such the selected weight class.
(keep gender in mind)
If you now play around whit the females weight you can see it's selecting the wrong weight classes,
I suspect based on "higher then" instead of "lower then".
This would also explain that if you type in a weight lower then 55 you get an "#N/A" error

Need you lot to tell me how to solve this though

Really appreciate the help so far.

***EDIT*** (again yes, cant stop messing around)

Interesting, if you change the "male" and "female" arguments around in the formula, the problem reverses as well.
Meaning the female values are now correct and the male values are incorrect.

Formula:

Please Login or Register  to view this content.

A cookie for who sorts this one out.

As for why the Female End Scores do not match the answer is the folllowing;
159.74/98= 1.63 Which is the coeffiecient of a class lower(up to 55 instead of up to 65) the same is true for
156.56/103= 1.52 Again a class lower then what it should be.

LOOKUP behaves the other way around as it does in the Male table. I know of no way to get it to behave differently for the Female table.

The only way I found to get the expected results was to shift the Female table coefficients upward and add another (lower) weight class. I hope this does not conflict with other purposes.

Female
Weight	coefficient
45	1.63
55	1.52
65	1.41
75	1.30
85	1.190000000

@ AliGW

You're welcome. Glad you like it.

Thomas62,

Here's another way.

Invert the original Female table like this.

Female
Weight	coefficient
95	1.190000000
85	1.30
75	1.41
65	1.52
55	1.63

Then use this new formula (which is actually 2 formulas wrapped inside CHOOSE) in P4:P8.

Formula:

Please Login or Register  to view this content.

This returns as sheet 'After' shows.

I am using Excel 2016 and I have stepped through Dave's original formula using the evaluate formula function and, as far as I can see, the correct coefficients are being used. Dave's formula results are in green and my calculations using the coefficient chosen by Dave's formula (which is correct) are in black at the end:

Excel 2016 (Windows) 32 bit

	G	H	O	P	Q	R	S
2	Gender	Weight	Total	End Score
3				= total * Coeffiecient		Coefficient used	O*R
4	Male	80.7	122	158.6	158.6	1.3	158.6
5	Male	75.6	119	166.6	166.6	1.4	166.6
6	Female	60	98	148.96	159.74	1.63	159.74
7	Male	95.1	134	160.8	160.8	1.2	160.8
8	Female	66.8	103	145.23	156.56	1.52	156.56

Sheet: After

You're welcome, Thomas!

You're welcome. Glad it's working out.