[ EXCEL | COMPUTER SCIENCE ] Part 1
Help extracting data in a manner that helps resolve other questions? (apologies, I think I used the word "extrapolating" wrong)

Question

kittybasil · Answer

Here is the file:

kittybasil · Answer

Questions Part 1 wrote:

The number of "Young Independents" (20 and younger; live alone)
Mean age of all females
Median Income Wage of those who did not go to college
The median Household Income of African American females. (Hint: you may want to create an intermediary column to calculate this)

kittybasil · Answer

Red text involves concepts that I am unfamiliar with. I know most of the formulas for the rest of the questions.

Questions Part 2 wrote:

"Next, please create a pivot table that grabs all the data. Put “Education Level” in values. Put the appropriate variable(s) in legend and axis to calculate the following: (Round to the nearest hundredth of a decimal if necessary)"

The number of Asian females who went to college
Of all American Indians, what percent only completed high school? (Hint: pretend there is a % sign after your number, e.g. enter 1/4 as 25.00, not as 0.25)
Of all White males, what percent attended college? (Hint: pretend there is a % sign after your number, e.g. enter 1/4 as 25.00, not as 0.25)
In our sample, females are more likely to be college students than males. Is this observation consistent within all the races in our sample? Pick ALL races for which females are more likely to be college students. (Options given: African American, American Indian, Asian, White, Other)
Create a pivot CHART to show your answer to the previous question graphically. Use the provided upload box to submit your pivot chart—you should be able to upload a screenshot, the Excel file, print it to a PDF, whatever works for you. (Hint: Your pivot chart should be a bar graph comparing the percentage of college students in each gender by race)

kittybasil · Answer

@Shadow @TheSmartOne @imqwerty

kittybasil · Answer

Formulas I'm aware of:

IF/Nested IF
SWITCH
Mean/median/mode

Anything else (especially the red parts) that I should know of/consider?

kittybasil · Answer

@Hero

Razor · Answer

I don't think I can help you completely. But I can maybe help a little if I understand?

Are you aware of these functions?

kittybasil · Answer

...I don't get the first two.

Razor · Answer

I never used those in my class. You won't have to worry about those unless you need to.

But in short, it's basically you putting in 1+2 and then the software should solve it for you, or with the other given 6/2 = 3 <- It'd give you that answer.

Does that make sense?

kittybasil · Answer

So treating Excel like your basic desk calculator... okay

Razor · Answer

Yes, exactly! Anything else you don't understand?

kittybasil · Answer

I mean, I was thinking stuff more along the line of IF and SWITCH as previously mentioned 😰

Razor · Answer

Hmm... Well, I know that the IF function is supposed to differ from a TRUE and FALSE statement or logical test. Let me pull up the Google Definition for you because they explain it better.

Razor · Answer

attachment

Razor · Answer

I've highlighted what makes more sense to narrow it down for you.

kittybasil · Answer

I know how to do those 😅 I just wondered if I needed more stuff like that to get all of these questions answered

Razor · Answer

Ohhhhh, I'm sorry. I misunderstood the situation. Well, I would say you could use the IF function when it comes down to comparing a hypothetical answer to another.

And the SWITCH function for one vs. many situations. Did that make sense?

kittybasil · Answer

Er... not what I meant 💀 are there more formulas I need to know to solve all of these?

Razor · Answer

Daaa... maybe the SUM or AVERAGE function when you think you need to use it. Other than that I don't think so

kittybasil · Answer

I mean, the stuff that throws me off the most is "the mean age of all females" how am I supposed to calculate that?

kittybasil · Answer

Admittedly I probably should know, but blanking right now

Razor · Answer

So am I, I'm sorry that it took so long for me to realize XD

kittybasil · Answer

Razor · Answer

The keyword is mean, so you'd need to use the MEAN/MODE/MEDIAN function. If that's what it's still called

kittybasil · Answer

It is, but idk how to calculate gender-specific because that's in another column entirely

kittybasil · Answer

I don't think formulas can apply to 2 columns at once

Razor · Answer

I'm not sure about that one

kittybasil · Answer

Ok I went and looked at the last time I did an Excel sheet like this and I figured out how to calculate that stuff

But still stuck on Pivot tables

Razor · Answer

What do you mean by stuck? How to make one or?

kittybasil · Answer

...wait how do I find the NUMBER of Young Dependents of all things? The list is cleaned up but how do you find specific numbers -- 😖

Razor · Answer

Does the assignment say you need to find specific numbers or to estimate

kittybasil · Answer

Excel data + questions list:

kittybasil · Answer

Ok internet tells me use COUNTIFS for the first question.

Razor · Answer

Pretty simple. For part 3 it says "consistent" meaning you won't need the EXACT amount. Or I'm pretty sure, like 98% sure about that one. Though you would need to use the AVERAGE function for that. If you can.

Razor · Answer

I forgot all about that one. GG

kittybasil · Answer

I think I get the gist of how to do these once I find the formula, but parameters are tricky things and they mess up EVERYTHING if you do them wrong. 💀

Hero · Answer

Using google sheets, use the DCOUNT function to find the number young dependents age 20 or younger. Use the  DAVERAGE function to find the mean age of all females. Then use the MEDIAN function to find the MEDIAN wage of those who did not go to college.

kittybasil · Answer

Ok, noted. I'll go work on those ASAP

kittybasil · Answer

read a DCOUNT manual and did not understand at all

kittybasil · Answer

What is this...?

kittybasil · Answer

Oh nvm, I found the Sheets infopage

Razor · Answer

:kanawhat:

kittybasil · Answer

^ ?

Nope, I still don't get DCOUNT.

Razor · Answer

attachment

Hero · Answer

It's only for numeric values, but you can convert Male and Female to numeric values by assigning MALE = 1 and Female = 2

kittybasil · Answer

I mean, I get what it's used for, but looking up how to use it (I also looked up YouTube videos) has not helped me understand at all

kittybasil · Answer

What about COUNTIFS( ?

Hero · Answer

Not sure if that works with G SHEETS

kittybasil · Answer

I mean, I looked up DCOUNT & it seems like it has too many parameters that I don't have

kittybasil · Answer

Hurrah! It worked!

kittybasil · Answer

...I don't know how to calculate MEAN with this though

Razor · Answer

Yay! I was at my limit and didn't know what else to do. But uh, what're you having trouble with MEAN wise

kittybasil · Answer

Oops, let me try DAVERAGE

Razor · Answer

Tell me how that works out

kittybasil · Answer

It said I have to put "MALE" when I put "FEMALE"
@Hero is this a parameter error?

kittybasil · Answer

Formula: =DAVERAGE(A2:A2500,"Female",B2:B2500)
Range 1 is the gender list, Range 2 is the age list

Razor · Answer

I think this is where my skills end unless I start googling things

kittybasil · Answer

Oh, I fixed it
Like this, right?

=DAVERAGE(A1:B2500,"AGE",I5:I6)

kittybasil · Answer

I5:I6 is a new cell set I used to specify criteria properly.
It looks like this:

Razor · Answer

It's like Access all over again. I think the formula looks right, I could be wrong. Don't take my word for it

kittybasil · Answer

I think I have those questions down. Stuck on the last one in Part 1 now...
"The median Household Income of African American females. (Hint: you may want to create an intermediary column to calculate this)"

Uh... what's an intermediary column?

Razor · Answer

The the... those charts that you use in the columns and rows. Er

kittybasil · Answer

Internal screaming

kittybasil · Answer

I really, really hate parameter errors.

Razor · Answer

UHHH what do you have so far?

kittybasil · Answer

For which one? @Razor

Razor · Answer

For you intermediary column?

kittybasil · Answer

Yeah - I don't even know, internet tells me nothing

kittybasil · Answer

Here's what I have so far on the question list:

Hero · Answer

It's easier to calculate things when you convert the categorical data to numerical data. It's important to know how to do that.

kittybasil · Answer

...?

kittybasil · Answer

Ok, this is getting a little long. I'll close this and make a new post

1 attachment

1 attachment

1 attachment

2 attachments

1 attachment

1 attachment

1 attachment

1 attachment

1 attachment