Multiple lookups (possibly XLOOKUP?) and then calculate within cell

[ollyhughes1982]

Hi all,

Re the file in the link at the bottom, and the images below. Please could someone help me find a way to automatically calculate the ‘Age Grade %’ (column F) of the ‘ALL COMPLETED nR ENTRY’ worksheet. What this column does is calculate the age grade percentage for a person, depending how fast they ran, taking into account gender and age.

Basically, I want to automate in column F, what I currently calculate within the separate ‘5K Age Grade Calculator’ worksheet. Each age grade calculation takes into account gender and age for calculation. When these are determined, the ‘Male 2010 Tables Time (Seconds)’ (column C) for males, or the ‘Female 2010 Tables Time (Seconds)’ (column H) for females number is taken and divided into the ‘Time Taken (Seconds)’ column (E for male or J for female), to give a percentage.

What I want to do is, automate checking whether it is a male or female (by checking the second letter of the value in column C of the ‘ALL COMPLETED nR ENTRY’ worksheet), then check the age, and then calculate using the correct cells from the ‘5K Age Grade Calculator’ worksheet.

I have manually done these calculations temporarily (in yellow), on the right-hand side. - The second letter of the ‘Age Group’ column determines Gender (‘M’ = Male / ‘W’ = Female).

I have manually inputted the values for column F at present (starting with the orange F4 cell).

The ‘5K Age Grade Calculator’ worksheet will remain in the worksheet for the references to calculate on.

Link (Small file, should open straight up):
Example Age Grade Calculator.xlsx

Thanks in advance,

Olly.

 

[ollyhughes1982]

I have now solved this, by just adding helper columns, rather than trying to calculate it all in cell. Thanks.