Hi,

This will probably turn out to be a very stupid question but I am literally at wit’s end. I have a problem in which I have to sort data (a group of students) and determine, using a formula, whether they are boys or girls. The results have to show up as y/n. The problem is that I’m learning Excel in a different language so I have no idea where to even start looking for a solution. If you could shed any light on this, that would be great!

Thank you!

So you don’t have any data in the table about the gender… the computer has to guess just based on names? If that’s the case, Excel can’t do it. You’d need, I don’t know, a Naive Bayes Classifier or whatever.

My instructions say that since GENDER isnt something Excel can calculate based on, I have to ‘‘assign’’ or ‘‘attribute’’ something to the names. Again, if this were in English this would be much easier to search. Maybe there is some arcane Logical function that can search based on Cell name?

I’m still confused about the information you have access to. Do you already know the gender of the students? If so, you’re done – Excel can evaluate whatever text string you like. Doesn’t matter if it says “boy”, “girl”, “y”, or “n”.

Say you’ve got 3 cells, A1, B1, C1.

A1 = Boy

B1 = Martal Lastname

C1 is the cell you want to do something in. You could do an IF formula to check what’s in A1, and output different values depending on what you have.

So you’d put in C1 this code:

=IF(A1=“Boy”, “Output A”, “Output B”)

Don’t know if that helps you. EDIT: Looking at your post again, it seems unlikely that the previous text would help. If you know python, NLTK has a little classifier. It’s only 75ish percent accurate. There are probably better solutions out there.

Does the data include phone numbers? You could ring and ask their gender, I guess.

I know the gender, based on the names, but Excel doesn’t know it. The columns I have to work with are basically just the names (there is other information such as grades, number of tests but nothing that can help me sort by gender).

So lets say in Column A I have :

Mike

Anna

Jennifer

Brad

and I need to get, into Column B, based on some criterion which I cant seem to figure out how to create, whether the name belongs to a boy or a girl. The only thing that I have come up with is name the cells with the boys’ names one thing and the one with girls’ names some thing and use a logical function based on that, but unless I’m missing something, that doesnt seem to be something you can do.

EDIT : I do know a bit of python but this is a question on an exam for a course on Microsoft Office :/

If its an exam question, rather a case of you having your fingers on the B and G keys and filling in a column really quickly, the only thing I can think of would to be having another table on another tab, with a column for first name, column for gender, then on the first tab a vlookup based on the first word of your original name list. This would work well if your original list was 2000 names long or something.

Thanks for the tip! I havent had to use VLOOKUP before but I’ll try to read up on and see how it can help me.

An alternative to VLOOKUP:

=INDEX(C1:C28,MATCH(A1,B1:B28,FALSE),1)

Edit: Oops, pasted the wrong formula.

I’d use playingwithknives suggestion. The only spanner in the works, so to speak, are names that can be both a boy’s and girl’s name.

A course on MS Office? I think you’re overly thinking the problem, misunderstanding it, or explaining it in a confusing way.

I can’t imagine an MS Office course requiring you to construct a practically-useable algorithm or table that assigns sex based on name, unless the universe of possible names is extremely small and non-ambiguous as to sex.

Are you sure you’re understanding the assignment right? Maybe all you need to do is create a column of data where you manually input sex based on your review of name. How many names are we talking about? Not every problem is most easily solved by trying to generate some function or an artificially-useable table that really only works for the specific problem set (i.e., basically you manually entering sex for each name, anyways, and then having Excel VLOOKUP on that).

As I understand it, it doesn’t necessarily have to be the sex that gets defined. I have heard that there might be alternative exercises, such as an exercise in which you have to sort a list of things that a household bought during one week by things that are food and aren’t food. The underlying principle is the same - determine whether X is Y or not.

If you guys say that this is truly something that Excel can’t calculate, that’s great. The number of names is very manageable. But the instructions, however, clearly state that the column in question must be filled using a formula.

I will report back tomorrow, when I can consult my professor.

Excel cannot “calculate” whether the name Mike is masculine or feminine. Excel can consult a table or array that you manually populated with the fact that Mike is masculine. There is little practical value in doing so, unless you create some super comprehensive database.

Looking up sex is a poor learning exercise.

I would say it is almost certainly what playingwithknives has said.

The exercise is to create a lookup table of names with male/female next to each one. You ‘make up’ whether each name should be male or female. Then you use the vlookup formula to map whether each name in your input list is male or female, based on the values in the lookup table.

To make things easier, you should copy and paste the list of input names and then use ‘remove duplicates’ to create the lookup table. This will insure you have all the names covered exactly once. Then just add another column and put in male/female at your discretion.

For added awesomeness, why not use this formula…

=IF(RAND()>0.5,“male”,“female”)

copy and paste to the column on the right of the name table and randomly assign gender just like nature does.

But I’ve learned a lot from it.

VLOOKUP fits the criteria to fill the column using a formula, it’s one of excel’s inbuilt functions. You do need to create a reference sheet of names or household items etc that the VLOOKUP function can reference.

In Excel terminology anything in a cell starting = is a formula, whether its basic addition ie =A1+A2, using Excel’s inbuilt functions like VLOOKUP or a custom written one in VBA. Functions can be nested, you aren’t limited to a single function or calculation within the formula.

You can reduce the amount of work by only populating half your data, ie only boys names and writing your function such that if VLOOKUP can’t find a match then it must be a girl’s name. There’s an obvious pitfall to that approach that you’ll mis classify your data if you omit a value in your reference data so you might be better off by accounting for that by flagging missing references with “not found”, or similar, instead. You could nest your VLOOKUP in a ISNA or ISERROR function to test for that.

The other night, I posted something in error upthread. Consider the INDEX-MATCH combo formula as well. Most people end up using VLOOKUP but it might not be powerful enough in some situations.

sorry guys, after consulting with the professor we discovered that it was a misunderstanding caused by poorly worded instructions. Thanks anyway.

Good to hear. I suspected as much, given that the exercise, as described, seemed poorly-crafted for teaching Excel. There’s better, and more practical, exercises for teaching concepts like VLOOKUP, if that was the goal.