Is there an Excel master here?

[sm0ke]

I need to figure out a couple formulas/functions in order to make this econ class project a little smoother and so I can pick up some skills along the way.

Basically, I have a column that has the mean wages for males and females in different age cohorts over time. I'm trying to get the wages in 2010 dollars. This implies dividing the wages by the proper CPI conversion factor according to the year (i.e. $100/0.064 for 1940).

What I need is either to get the original column in real wages (without manually going through and converting each cell) OR creating a new column with the values of the mean wage cells converted into 2010 dollars (again, without having to go through each cell and manually typing "=(E2)/0.064" and so on, for each cell.

Ideally, I'd like to have a formula that can look at the cell two rows to the left to see what year it is, and THEN divide the cell by the proper conversion factor.

I also might need help turning this into a cohort line graph, but that's not the issue at the moment.

 

[Slaimon Khan Shah]

THIS IS SOMETHING I MAY WANT TO ACCUIRE AS A SKILL.

 

[BeerOnTheBeach]

I suggest you watch some of this guys videos, he pretty much covers EVERYTHING. Has immensely improved my Excel skills with ease.

http://www.youtube.com/user/ExcelIsFun

 

[Sensitive Blake Griffin]

depends how you set it up.. you could use a lookup function on the date which then looks up the factor for that year and have that lookup function nested within the division problem? not sure if that would work or not but should give you something to think about. I'm stoned and it's late hope it helps.

 

[newworldafro]

video search it breh.........














The potentcy of the innanets .......

 

[Sensitive Blake Griffin]

or try using an index and match function for the date and corresponding CPI

 

[Prodigital]

depends how you set it up.. you could use a lookup function on the date which then looks up the factor for that year and have that lookup function nested within the division problem? not sure if that would work or not but should give you something to think about. I'm stoned and it's late hope it helps.

c/s your pretty much on point help wise for this guy

OP, get familiar with how the $ sign works in excel. Once you get that, you should be able to copy and past a quick nested function and do this shyt in one line of code.

 

[Sensitive Blake Griffin]

if it's all in the E column it would be $E2

 

[you're NOT "n!ggas"]

this sounds like something you could do with data validation/vlookup... where you'd create a drop down list of the years and their formulas off to the side somewhere so that when you click '2010', the numbers in the original column adjust to it... but i gotta be real i'm pretty ******ish with excel, there's probably a better way

 

[Rickdogg44]

Like people said above. Either you can anchor a cell in the formula or lookup a value based on your year. All on how the data is organized.

I swear 95% shyt I do is normalizing data, organizing data, vlookup, match, index, sum, pivots, nested ifs, and sumproduct/sumifs. Nothing extremely complicated.

 

[sm0ke]

I did something, it seems to work. Just a bunch of IF statements strung together. Really ugly, but... good enough, I suppose. Now I'll have to test a couple of the figures out to see if the numbers work.

=IF(A:A=1940,(E4)/0.064, IF(A:A=1950,(E4)/0.111, IF(A:A=1960,(E4)/0.136, IF(A:A=1970,(E4)/0.178, IF(A:A=1980,(E4)/0.378, IF(A:A=1990,(E4)/0.599, IF(A:A=2000,(E4)/0.79, IF(A:A=2005,(E4)/0.896, IF(A:A=2010,(E4)/1)))))))))

Pos rep + daps to all that helped. This thread may not be done just yet.

If someone can think of something easier, I'd be glad to hear it. Cleaning this up would help ensure that my data isn't screwy.

 

[sm0ke]

Thinking about it, I could have set up a column to display the conversion factor, depending on what the same-row cell in column A (Year) contained, then the subsequent columns could display the data in the wage columns by dividing the nominal wage column by the factor column. I need to brush up on my excel lingo, I know I sound like an idiot.

Still a little jerry-riggish, but I might try it that way instead. It's just a little bit cleaner.

 

[Rickdogg44]

I did something, it seems to work. Just a bunch of IF statements strung together. Really ugly, but... good enough, I suppose. Now I'll have to test a couple of the figures out to see if the numbers work.

=IF(A:A=1940,(E4)/0.064, IF(A:A=1950,(E4)/0.111, IF(A:A=1960,(E4)/0.136, IF(A:A=1970,(E4)/0.178, IF(A:A=1980,(E4)/0.378, IF(A:A=1990,(E4)/0.599, IF(A:A=2000,(E4)/0.79, IF(A:A=2005,(E4)/0.896, IF(A:A=2010,(E4)/1)))))))))

Pos rep + daps to all that helped. This thread may not be done just yet.

If someone can think of something easier, I'd be glad to hear it. Cleaning this up would help ensure that my data isn't screwy.

You may be making it too hard breh. Plus it may be hard to follow (if you have to come back to it later)...

I would create a newtab with the years in Column A and corresponding Factor in Column B.

So that big nested if statement you did above would be:

=E4/VLOOKUP(<year cell value...assuming A4>,newtab!$A:$B,2,FALSE) ... anchor accordingly as needed on how your data is organized

Now...if for some reason 1940 factor needs to be changed ... you can just change it in one spot on the newtab vs everywhere in your analysis/formula(s)

I'm lazy/effecient/hate re-work

 

[sm0ke]

You may be making it too hard breh. Plus it may be hard to follow (if you have to come back to it later)...

I would create a newtab with the years in Column A and corresponding Factor in Column B.

So that big nested if statement you did above would be:

=E4/VLOOKUP(<year cell value...assuming A4>,newtab!$A:$B,2,FALSE) ... anchor accordingly as needed on how your data is organized

Now...if for some reason 1940 factor needs to be changed ... you can just change it in one spot on the newtab vs everywhere in your analysis/formula(s)

I'm lazy/effecient/hate re-work

Not gonna lie, I don't know what any of that means after "newtab!"

I redid it, though, and did create a separate column with the conversion factors (using another nested if statement for 1940,50,60,70,80,90,05 and 2010), and the subsequent columns are just =(E:E/I:I), where E is the conversion factor column, and I is wage/income cell that I'm converting.

 

[sm0ke]

You may be making it too hard breh. Plus it may be hard to follow (if you have to come back to it later)...

I would create a newtab with the years in Column A and corresponding Factor in Column B.

So that big nested if statement you did above would be:

=E4/VLOOKUP(<year cell value...assuming A4>,newtab!$A:$B,2,FALSE) ... anchor accordingly as needed on how your data is organized

Now...if for some reason 1940 factor needs to be changed ... you can just change it in one spot on the newtab vs everywhere in your analysis/formula(s)

I'm lazy/effecient/hate re-work

I took your advice and created a new tab for the adjusted dollars, bringing over only the year, gender, and cohort info. still using the nested if function for the conversion factor column, but it's pretty clean compared to what it was an hour ago.