Excel Question

[gstelmack]

Say I have a spreadsheet where some of the cell values are words. Say those words are ratings that could be mapped to integers like "Average", "Fair", "Excellent", etc. Say you wanted to write a formula that produced a rating based on these cell values.

Is there anyway to easily map a string value in a cell to a number that can be used in calculations? Short of doing a search-and-replace? I'd like to still see the word values, but get a numeric rating that I can sort on.

It would eliminate one step (the search-and-replace) in me rating my scouts / coaches without having to write the "FOFStaffAnalyzer" utility (which would really be overkill for this).

[wade moore]

Very easy.... do you use AIM? If so, add me (wademoore) and I'll explain it (a pain to do in a post)

[gstelmack]

Nope, I don't have any instant messaging accounts. Sorry.

[wade moore]

Alright.. let me try to make an example.. it's not going to be clean, but it will work..

[gstelmack]

Cool, thanks. If I get pointed in the right direction I can probably figure the rest out.

[JimboJ]

You need to use the LOOKUP function. You basically need to set up a mini table in your spreadsheet with the integer values and their corresponding words. You then use the LOOKUP function to look up the word that corresponds with the integer.

Do a search on LOOKUP in Excel help. They give a couple of examples there.

[FrogMan]

what JimboJ said, either lookup or vlookup would work...

From the excel help:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup_value is what you are looking to replace
table_array is the table in which your numerical values are. vlookup will be looking in the first column of that table, so the text you want to replace should be in that first column
col_index_num is the column you want to return. In your simple case, that would be 2, but you could have more than one column and ask the function to return you a specific column
range_lookup is a true/false where if set to true, it will return the closest value, if set to false, it will return the exact value, or a #N/A message if not found...

Hope this helps...

FM

[wade moore]

What he said...

[SonyLuang]

damn u guys are smart

[FrogMan]

err, no Sony, that's what I do for a living, play with excel for long parts of the workday

FM

[fantastic flying froggies]

Originally Posted by FrogMan

err, no Sony, that's what I do for a living, play with excel for long parts of the workday

FM

You're one lucky man FM !

[gottimd]

Originally Posted by gstelmack

Cool, thanks. If I get pointed in the right direction I can probably figure the rest out.

Gstelmack-

If you are stuck I can help, I do what sounds like the same thing Frogman does all day long. I build Financial Models using Excel with VBA for my company. Also, here is a little site I find useful for excel questions if you get stuck and no one has answered you, or your stuck and you know it can be done, you just don't know how.......

Mr Excel Message Board

A board much like this one related to all Access and Excel etc problems and solutions. Its free to and people usually respond pretty fast.

Hope that helps.

[FrogMan]

Originally Posted by fantastic flying froggies

You're one lucky man FM !

hehe, I know At least for me, that's a perfect job

I didn't have an example online at the time I posted my answer, but I have one now. It's simply translates the text value for the match report ratings in Hattrick into numerical values. You don't have to care about Hattrick to learn from it

There you go:
http://webhome.idirect.com/~stevegou...atingscalc.xls

FM

[sterlingice]

I've gotten a lot of good help from the Excel newsgroups online. The people who post there are quite friendly and patient as they realize not everyone is as advanced as they are. But, I would recommend searching old messages first.

SI

[gstelmack]

Thanks a bunch guys. This should definitely get me rolling.