Question

[ Computer Science | EXCEL ] Part 2
Which formulas should I use to extract data that can help me answer these questions? Files attached in first comment

Answer 1

Data file: Excel spreadsheet (I'm using Google Sheets)
Questions: PDF. I am on the last question of Part 1

Answer 2

@Hero sorry, what were you saying about numerical conversion?

Answer 3

Also, if anyone can check that my work so far is correct... 😅

Answer 4

Something just occurred to me about the pivot chart thing. Do you know where you can insert one at?

Answer 5

Oh great. Does Sheets not have this function...

Answer 6

FFff- what are you using, thought Excel.

Answer 7

Found it

Answer 8

Nah, Hero told me Sheets is easier. And it is, tbh

Answer 9

Oh sweet. Well with the information you have now, I assume you just apply it to the pivot table they give you or the ones that are available, right? I dunno I never used google sheets.

Answer 10

Oh no... this pivot table is complicated 💀

Answer 11

:kanaoh: ._.

Answer 12

That didn't work.

Answer 13

I'm working on the Pivot table now

Answer 14

But I don't get Part 1's last question yet... and part 3 also makes little sense

Answer 15

This?

Answer 16

You have to make a separate column with the income of African American Females income only. Then use the MEDIAN function on that column.

Answer 17

@Razor Yeah, that's part 3

Answer 18

I do understand that, but I'm not sure how to work that out @Hero

Answer 19

Type it out manually. It shouldn't be that much data to copy and paste to a new column.

Answer 20

There are 2500 rows

Answer 21

Another option is to use the SORT function to order the data for the Race column. That way you can easily copy the data

Answer 22

So I was wondering if there's a grab-all formula...

Answer 23

Oh? I will look into that

Answer 24

Yes use the SORT function. First do it for the race column. Then do it again for the Gender column.

Answer 25

Okay, thank you. I'll try that

Answer 26

For the gender column you have to sort the data only for the particular range that you want which is African Americans only

Answer 27

Wait, so... SORT the race column to African-Americans, then select the relevant parts of the gender column to SORT?

Answer 28

Da... I'm assuming he means for each race take each gender together and- Wait...

Answer 29

or something like that

Answer 30

Um, no. He said SORT function 😂

Answer 31

Thank you for trying to help explain, though

Answer 32

Yes

Answer 33

There's a multi-column options here, would that work?

...wait, so it's the menu option or the Function described in that link? ^

Answer 34

No, don't do multi-column.

Answer 35

Eh? What would that do?

Answer 36

Oh, right. So single-column SORT function?

Answer 37

Correct

Answer 38

This isn't the same as the right-click dialog "Sort range" is it?

Answer 39

You ask a lot of questions. You could have already finished this task already.

Answer 40

I have to make sure before I make an error... 😰

Answer 41

All the other stuff is confusion right now for you. Just learn how to use the single column SORT function first. Afterwards, you can learn how to use the other related functions.

Answer 42

👌🏻 Got it

Answer 43

If I say "Perform this particular task", then you say, "What about this and this and that", you're setting yourself up to be confused about what needs to be done.

Answer 44

Just need to make things clear, I had a parameter error earlier on so I wanted to avoid making unnecessary mess.

I have the Race column sorted

Answer 45

Very good. Now sort the data for the gender column but only for the range of African American rows.

Answer 46

The right click might work for that

Answer 47

... I think I might have done something wrong. How would I do that?

Answer 48

Did you sort the GENDER Column yet?

Answer 49

I don't think I am able to sort it

Answer 50

My Race column is entirely outside the data table... I attempted to put the function into the table but it gave me an error

Answer 51

That's impossible. The African American data is only a subset of the data

Answer 52

The red-circled is my SORT column

Answer 53

Should I use the right-click dialog "Sort Range" instead?

Answer 54

I already mentioned the right click option might work for the gender column.

Answer 55

Add Another sort column.

Answer 56

Okay, so I sorted 3 columns since there was something else in the middle

Answer 57

Oh I shouldn't remove the crossed-out section?

Answer 58

It's only got "A-Z" or "Z-A" formatting though...

Answer 59

Yes remove Household Income You're only sorting Race and Gender

Answer 60

Er... I don't think I'm understanding. Like this?

Answer 61

ONly thing is, It's not going to work that way.

Answer 62

Oh, I just sorted the Race column and everything in the other 2 columns was sorted with it. I guess that works

Answer 63

You have to do it the way I mentioned originally.

Answer 64

Share the sorted document.

Answer 65

Okay, here's the sorted data:

Answer 66

You need to sort African American Females

Answer 67

...oh, right. 🤦🏻‍♀️

Answer 68

Apologies. I think I have it now

Answer 69

Oh, this question thread is getting a little long. I'll make a new one after I finish this problem...

Answer 70

It's sort by Race then by gender. You have it backwards.

Answer 71

Darn, I think I made an error in another question...
Er, would it make a difference if they are backwards?

Answer 72

Yes. Order matters when it comes to sorting.

Answer 73

Ah, ok. I'll fix that after I fix the other question...

Answer 74

Screenshot afterwards. Make sure Columns of Race and Gender are adjacent to each other.

Answer 75

For "Median Income Wage of those who did not go to college" I realized I had not calculated the second factor in, is this the proper sorting to get the correct median?

Answer 76

I also moved the columns adjacent for this one

Answer 77

It doesn't matter whether you sort A-Z or Z-A in this case, as long as those who went to college are excluded from the MEDIAN calculation

Answer 78

Okay now back to what we were working on.

Answer 79

I think I have both of them; I'll update the open question post when I finish working on these two Sort questions

Answer 80

Ok, I finished
By the way, I got the same result with Race-Gender as I did with Gender-Race

But I'll follow the order of categories stated from now on.

Answer 81

Screenshot

Answer 82

@kittybasil

Answer 83

Oh. Er... I already hit undo because I moved on to the Pivot table. 💀
One second...

Answer 84

It's not right unless I approve

Answer 85

Okay...

Answer 86

Here's the sorting.

Answer 87

No, that's not right. If it were right, it would look something like this:

African American Female
African American Female

Answer 88

You did not correctly sort gender

Answer 89

Eh? That was prior to sorting. I also uploaded a screenshot of my sorting method

Answer 90

Why would I want anything PRIOR to sorting?

Answer 91

I thought you had wanted to see if I did things correctly

Answer 92

Here is the post-sorting

Answer 93

I moved all respective columns adjacent and then sorted them accordingly, and that's what I got

Answer 94

All I want is the correct sorting. I don't need to see anything prior to that. Yes you have it correct now finally.

Answer 95

I'm just sharing it so it makes sense if I made any errors in the process... I guess it was fortunate I had not

Answer 96

Should I post results of the "Median Income Wage of those who did not go to college" question as well?

Answer 97

I'm pretending to be your boss by the way. That's how they will talk to you when you are working for them.

Answer 98

Thanks, but this isn't a workplace assignment though...

Answer 99

The assignment is workplace TRAINING. Everything you're doing is Workplace TRAINING.

Answer 100

What do you mean? I won't be doing this in the workplace.

Answer 101

Hence the word "Training". No one said you will be asked to do this particular task in the workplace. However, as part of a project you might work on for a company, it may require knowing how to Sort data in the same manner as you are being tasked with now.

Answer 102

Ok, understandable. But... I am on here asking for help on each step so I don't make absolutely ridiculous errors when I do have to experience that scenario in a workplace setting...

That's fine, right?

Answer 103

\(\color{#0cbb34}{\text{Originally Posted by}}\) @kittybasil
Should I post results of the "Median Income Wage of those who did not go to college" question as well?
\(\color{#0cbb34}{\text{End of Quote}}\)
If the results of this calculation is the answer to one of the questions you've been asked to answer, then yes.

Answer 104

I don't need to see it since I was only helping you with the sorting.

Answer 105

Now that you've completed the sorting task, you are now able to calculate the Median of African American women income.

Answer 106

Median income of those who didn't go to college:

Answer 107

That's a list of income of those who did not go to college, but not the median income. You have to calculate that using the MEDIAN function in excel.

Answer 108

I have that done:

Answer 109

This is also in the other post. I'll go there now.