OK, so I have a slight brainfart. I used to know how to program very well in Excel, but have forgotten how to do a lot of it. Here is my problem:

Each day, I am keeping track of market prices for my 401(k) for the government called TSP. I am keeping track of the fund prices. My money is spread between five funds. Each day I go to http://www.tsp.gov/rates/share-prices.html and copy everything from the G fund to the I fund for that day's prices. I then paste this into a row into Excel. At the end of each row, I have a rolled up total for that day's prices depending on how my money is allocated for that day.

For example, yesterday I was 50% in the S fund, and 50% in the I fund. I multiplied each of those percentages by that day's fund prices, and I get a gain or loss for that day. Then I turn those figures into a percentage, which is rolled into the last cell in that row for that particular day?

Follow me so far?

I have an overall year's total for the year at the bottom of the Excel spreadsheet. This total is just the last number in that column. For example, the last number is this column is the percentage from the share prices from yesterday. Instead of updating this number every day, how can I take the last number in a COLUMN and put it into a specific cell. To visualize:

34
32
12
6
32
56
32
3
21
3
3
64



Total: 64

Instead of manually putting '64' in every time (or whatever the last number is in the above column), how can I get Excel to update automatically the total (which is NOT a sum) after I add another number after '64?' (and so forth)

Reps to those who figure it out!

 

 

is it a coincidence that in your example here you have 64 listed as the last number and it's also listed as the 'Total'?

If you made a formula to calculate it for you and then just either left a bunch of rows blank for the rest of the month everytime you filled in another row it wouldn't it update? or if you just inserted a new row each day, it should modify the formula to reflect the newly created row.

Those are the simplest fixes i can think of...

edit: after reading it again - I realized that my above answer doesn't cover it at all...

what do you do with your numbers to come up with the 64?

It's hard for me to visualize an excel problem when i'm not looking at it directly in excel lol...

 

there is a range function within excel, and you could use that to find the range of cells that you are talking about and then use the row number and the max functions to get you the value you want. it would require a little bit of VBA or a macro to do this way, but it is possible. Prolly not the solution you were looking for, but since i quit being a code monkey i have forgotten a lot about VBA.

The real question is, if the 'Total' is just the last value in the column, why bother reprinting it somewhere else? seems like overcomplicating something to me.

 

1.select the square you want the total to be in.

2. in that square put =SUM(d2:d300)-------don't know what row u need it to be, so i used d

3. from then on, anytime you enter in a number into row d, excel will ad it to the total


lemme know if that helps

 

The total of '64' is the last value in a column. That's it.

Instead of updating the total, how do I get Excel to do it automatically?

Also, the rows are already there. I'm just copying and pasting data, and not creating new rows.

 

Sorry, but nope. There is no correlation between rows of data. If there was I would be able to predict stock prices pretty easily. For example:

23
32
64
34
23
1
43
42
78

Overall return: 78
In the above data, 23 has no correlation with 32, none with 64, etc. I just simply want to return the last value in a column. Thanks for trying!

 

^^ he said it wasn't a sum

so you want the total just to reflect the last value in the column at all times? ahh... i c...

 

Ed Zachery!

(For those of you who didn't get that, watch the movie "Big Trouble!")

 

now if I could only figure out the solution to your problem...

It's definitely tougher than I understood @ first...

 

so in the box that the total is in do =SUM(last square:an unused blank square)

 

No it's not a sum.

 

^ that's what makes it difficult. It would be easy if it was a sum.

Man when you don't use excel for a while you really forget the basics eh? this used to be easy...

 

i know it's not a sum, but if u just add the number u want in the box + 0, then u get the number u want in the box

 

I tried to do an IF statement, but I don't know how to return the last value, as in:

=IF(NULL,,last_value)

Doesn't work.

 

Still won't work because I need to do a range of values. If I do a range of values in your situation, I still end up with a sum.

In a range of values in a column, I need the last value.

 

in total square use a function...

ie: D2=D78

 

Like I said, need something like that but with a range of values within a column.

What you said will only work for one day, not tomorrow.

 

Is the last value (the "total") Always the highest number? If so, you can do a LARGE function.


=LARGE( ..Select the entire range (including null values) up to the total column.., 1)

Example...

you have data in cells A1 through A20... Your total cell is in A30, you would do:

=LARGE(A1:A29,1)

 

If you're just copying this to the next column, it here any reason why you can't just drag the equation across the entire row?

Also, what might make this easier is some pictures...like the ones you did for when you were fighting with the wife over the thermostat in the house...

 

Not the highest.

These are stock market percentages, so they could be high, low, or even negative. That's why this is so confusing. There seems to be no correlation or function to do this...

 

If you have an extra column, use the formula =IF(A1-A2=A1,A1,0) somewhere off to the side (last column for example) wherein your list starts at A1. it will display a '0' for every one that isn't the last number and will display the last number for the last number. then, in the cell you want the last number to be displayed, use =max(C:C)+min(C:C) (or whatever colum you put that formula in) and it'll show it for you.

 

MAX will retrurn the largest value, not the last value

 

<a href="http://img339.imageshack.us/my.php?image=excelry5.png" target="_blank"><img src="http://img339.imageshack.us/img339/3590/excelry5.th.png" border="0" alt="Free Image Hosting at www.ImageShack.us" /></a>

 

the formula I have will show the last number because it will be the only non-zero. positive or negative.

 

Ahh, gotcha. Yeah, this will work if there are extra columns to use.

 

lol...I hope he hasn't used all 256 columns!

 

OK, an explanation about the picture.

See where it says in light green "Fund Price?" That's what I update every day. I copy and paste values, and do NOT insert more rows into the spreadsheet.

Those fund values are multiplied by what's in yellow, or the "Fund Allocation."

Then the values in orange, or "Fund Returns," are the fund price for today's date - the fund price for yesterday/the fund price for yesterday, i.e. for cell G222,

the formula is = (D222-D221)/D221.

"My Period Return," in blue, is just what's in orange multiplied by what's in yellow for each fund. Each of the returns is added together. For example, in cell T222, we have

=((I222*N222)+(J222*O222)+(K222*P222)+(L222*Q222)+ (M222*R222)).

What's in purple is what I'm trying to get the last value of, i.e. the latest of 7.24% (as of today). Yesterday it was 6.74%.

 

Well, the fund return could be zero though too!

 

I see zero zeros on there.

 

If I allocate 100% to the G-fund, then if the G-fund doesn't gain anything like it didn't for yesterday, then my fund return would be zero. What then?

 

stop putting all your eggs in one basket. that's what.

 

Have you tried the VLOOKUP or HLOOKUP Functions? I haven't looked at your spreadsheet to in depth yet. It allows you to specify a range of cells and choose a value in that row. First thing that comes to the top of my head.

 

G-fund is a guaranteed fund. It does not lose value. It gains a penny every week. For example, if I happen to think that all funds will lose value on Tuesday, I will allocate 100% to the G-fund to pick up a guaranteed penny.

 

Won't work because I want Excel to choose that value for me, and that value changes daily.

 

OK gatrbastard...here ya go.

- insert a column to the left of your return numbers (you can hide it later)
- fill down 1,2,3 until you get to the bottom (or however numbers you're going to have
- in a blank cell off to the side, use the formula = count(A:A) (or whatever column your returns are in)
- in the cell you want the final number to be in, use the formula =VLOOKUP(E1,A:B,2) wherein E1 is the cell I have the =count(A:A) in, A is the column my 1,2,3.... is in and B is the column the returns are in.

 

gotta use it right, home dawg.

 

Let me try that...

 

One of the functions in the LOOKUP & Reference category will work. VLOOKUP and HLOOKUP are two of the easier ones. I would suggest checking those functions out. As revitupwriteitoff suggested, you may have to make some changes to the spreadsheet to get it to work, but one of those should work for what you are trying to do.

 

Here you go...

=INDEX(B:B,MATCH(9.99999999999999E+307,B:B))

Just replace the "B:B" with the actual range of values and Excel will automatically pull the last value in the selected range.