Percentages with 3 columns

C

Cleo

New member
Joined
Nov 6, 2013
Messages
9
Reaction score
0
Points
0
I am using Excel 2008 on a MAC. I have 3 columns: Census tract, Total number of people, and People living below the poverty line. I want to find all of the census tracts where 50% or more of the people live below the poverty line. Help! (also posted this in "Other General Questions")
 
What do you mean by "find"? Do you want to highlight, add a flag in a column? Count Matches? Sum Matches?
 
I need to identify those tracts where 50% or more of the people live below the poverty line, then make two new columns... One that shows the identified tract numbers and the other that shows the number of people living below the poverty line within each tract.
 
Can you attach a sample workbook? No confidential info please.
 
Yes, I am happy to attach a sample workbook, but I don't see how to do that!
 
Click Go Advanced below the Reply box, then the paperclip icon, then browse and upload the file.
 
The columns I am working with are A, B and C
 

Attachments

  • B17001-Poverty Level in past 12 months.xlsx
    16.5 KB · Views: 14
Ok, if I understand correctly, try this.

First, add a helper column to identify the matches.

In F4 enter formula:

=IF(C4/B4<=0.5,COUNT(F$3:F3)+1,"")

copied down.

Then in say, I2:

=IFERROR(INDEX($A$4:$A$190,MATCH(ROWS(I$2:I2),$F$4:$F$190,0)),"")

copied down same distance as database table.

in J2:

=IFERROR(INDEX($C$4:$C$190,MATCH(ROWS(I$2:I2),$F$4:$F$190,0)),"")

copied down same.

These can be Cut and pasted in Sheet2, if desired.

Is that what you need?
 
Thanks for sending this. Unfortunately, I don't know where to find F4, 12 and J2. By helper column do you just mean a new empty column?
 
Yes, I mean a helper column. Those are cell references.

See attached.
 

Attachments

  • Copy of B17001-Poverty Level in past 12 months.xlsx
    26.3 KB · Views: 11
Thanks. I see that you labeled columns I and J "Tracts below 50% Poverty". I need 50% or more of the people who live below the poverty line, so that would be "tracts above 50% poverty". Is that what this is?
 
Oh I see, you didn't actually do it, just showed me what 2 new columns look like. So when I put those formulas into the cells referenced (and copy down) and hit enter nothing happens except a 1 shows up in the cell
 
I did fill in the formulas.. and you should see data in columns F, I and J...

Change the formula in F4 to:

=IF(C4/B4>=0.5,COUNT(F$3:F3)+1,"")

copied down.

then formulas in I and J should change accordingly. Just change the headers to suit what is actually being pulled
 
I put that formula in the F4 cell and highlighted down. But that was wrong. I guess I don't know what copied down means. I am in over my head.
 
You should only see sparsely laid out consecutive numbers... they only appear where there is a match to the check that column C divided by column B is greater than or equal to 0.5 (50%). The first one appears in row 10.

I uploaded the revised workbook.

To copy down a formula you select the cell that has the formula, then click, hold and drag down the little black square at bottom right corner of the cell. (you can also double-click it and it will copy it down as long as the cells to the left are not empty).
 

Attachments

  • Copy of B17001-Poverty Level in past 12 months.xlsx
    25.4 KB · Views: 12
You must log in or register to reply here.
Back
Top