Using a name tag in a formula

[mpanty]

Hi.

I was wondering if, instead of using the usual cell code such as A1, B12, L14 etc, if there was a way to use a column heading in a formula instead, and have Excel recognize it. This will probably look more clear with an example.

Say I have the following table, with different player abilities:

Name / Shooting / Tackling / Passing
Marc / 4 / 5 / 10
Jason / 5 / 8 / 9
Jake / 10 / 3 / 6

And I want to use a formula of who's the best defender, forward etc.
So for a forward I would use
=(B2*5+B3*1+B4*3)/5+1+3
(the numbers are just modifiers to give one skill more importance than the others)

My question is: is there a way I can write the same formula, but using for example:
=('shooting'*5+'tackling'*1+'passing'*3)/5+1+3

Can I tell Excel to give one column the parameter "shooting", another column the parameter "tackling" etc. such that when I write 'tackling' in a formula, Excel knows what I'm talking about?

That way, it would be a lot easier for me to re-edit the formula later and pinpoint which skills to give more weight to (think that I have to use many many other skill adjectives).

Any help is appreciated.

[TQO23]

Hi Mpanty

OK new to Excel myself so someone will probably give you a much better way but have a look at "Insert" "Name" "Define". I think this will give you what you want. I set up the cells you quoted and then put this formula in the result cell, works for me:-

=(Shooting+Passing+Tackling)/5+1+3

Regards

TQO23
Nigel Hanwell

Thought - You could also set up a list of "weightings" called skill1, skill 2 etc and define these so your formula would look something like =(Shooting+Passing+Tackling)/skill1+skill2+skill3. All you need to do then is amend the skill parameter.

Hope this helps.

[mpanty]

Thanks a lot Nigel! Worked just great.

About your second suggestion, I'm not too sure on how to apply it. Should I create two new columns, the first one with the skill list, and right next to that, the "weightings" I wish to give to each skill?

I guess that could work, but it would probably be unpractical simply due to the way I've arranged my table. Not to mention that I'd like to have formulas for several field positions (defender, forward, goalkeeper etc.), for which the weightings are going to be different. It's all feasible though, so I'll keep your idea in mind, and see if I can implement everything... Thanks again!

[TQO23]

Yep exactly that Mpanty.

eg. Column H10 would be Defender Skill1, in I10 you put the weighting value but you name define I10 as Def_Skill1 or something similar. That way you could have a small table for each position with different weightings.

Hope this would work for you.

Best of luck.

TQO23
Nigel Hanwell