taking percentages
 

Ok, I have a 401k database set up for an entire month and I have been trying
to figure out how in the world to set each day up to when you post your new
earnings for the day, a new percentage would pop up showing how much you
have gained or lost in that certain fund for the month. I was wanting to do
a percentage for each fund as well as for the grand total of all of them.
Also when the total is 0 I wanted to omit those out of the percentage,
because you all know on the weekends nothing is posted. Also I just want it
at the bottom so it is listed only once for the whole month, it doesnt have
to be beside each individual day. Thanks a bunch.

taking percentages
 

here is an example of how its set up and what i would like for it to look
like....
date 3/13/2007 date 3/14/2007
PERCENTAGE
401k1 32000 401k1 50000 I want to take all of these
401k1 22%
401k2 56000 401k2 56000 figures individually and
401k2 0%
401k3 45000 401k3 45000 get the daily percentage
401k3 0%
401k4 34000 401k4 34000 increase or decrease
401k4 0%
total 167000 total 180000
total 22%

"Zr2x350" wrote:

Ok, I have a 401k database set up for an entire month and I have been trying
to figure out how in the world to set each day up to when you post your new
earnings for the day, a new percentage would pop up showing how much you
have gained or lost in that certain fund for the month. I was wanting to do
a percentage for each fund as well as for the grand total of all of them.
Also when the total is 0 I wanted to omit those out of the percentage,
because you all know on the weekends nothing is posted. Also I just want it
at the bottom so it is listed only once for the whole month, it doesnt have
to be beside each individual day. Thanks a bunch.

taking percentages
 

"Zr2x350" wrote:

here is an example of how its set up and what i would like for it to look
like....
date 3/13/2007
401k1 1000
401k2 1000
401k3 2000
401k4 3000
t0tal 7000

date 3/14/2007
401k1 2000
401k2 1000
401k3 2000
401k4 5000
total 10000

then in a box by itself I would like to be able to keep up with the daily
percentage drop/increase in each section
PERCENTAGE increase/decrease
401k1 100%
401k2 0%
401k3 o%
401k4 75%
total 120%

Can someone help me with this issue please. Thanks
"Zr2x350" wrote:

Ok, I have a 401k database set up for an entire month and I have been trying
to figure out how in the world to set each day up to when you post your new
earnings for the day, a new percentage would pop up showing how much you
have gained or lost in that certain fund for the month. I was wanting to do
a percentage for each fund as well as for the grand total of all of them.
Also when the total is 0 I wanted to omit those out of the percentage,
because you all know on the weekends nothing is posted. Also I just want it
at the bottom so it is listed only once for the whole month, it doesnt have
to be beside each individual day. Thanks a bunch.

taking percentages
 

Use VLOOKUP:

Not sure how data is organised but assume data for 3/13/07 is in columns A&B
and for 3/13/07 in D&E.

Let column F contain your 401 codes:

Then percentage change: =((VLOOKUP,F1,$D:$E,2,0)/VLOOKUP(F1,$A:$B,2,0))-1


Format cell as percentage


(Some of the % in your sample are wrong: % change = (new value - old
value)/old value)

HTH

"Zr2x350" wrote:



"Zr2x350" wrote:

here is an example of how its set up and what i would like for it to look
like....
date 3/13/2007
401k1 1000
401k2 1000
401k3 2000
401k4 3000
t0tal 7000

date 3/14/2007
401k1 2000
401k2 1000
401k3 2000
401k4 5000
total 10000

then in a box by itself I would like to be able to keep up with the daily
percentage drop/increase in each section
PERCENTAGE increase/decrease
401k1 100%
401k2 0%
401k3 o%
401k4 75%
total 120%

Can someone help me with this issue please. Thanks
"Zr2x350" wrote:

Ok, I have a 401k database set up for an entire month and I have been trying
to figure out how in the world to set each day up to when you post your new
earnings for the day, a new percentage would pop up showing how much you
have gained or lost in that certain fund for the month. I was wanting to do
a percentage for each fund as well as for the grand total of all of them.
Also when the total is 0 I wanted to omit those out of the percentage,
because you all know on the weekends nothing is posted. Also I just want it
at the bottom so it is listed only once for the whole month, it doesnt have
to be beside each individual day. Thanks a bunch.

taking percentages
 

Yeah I just wrote those percentages down for an example, i didnt actually
figure them I just wanted you guys to get the idea of the layout. So how
exactly does this VLOOKUP: work? you just type in the figures you want it to
work?

"Toppers" wrote:

Use VLOOKUP:

Not sure how data is organised but assume data for 3/13/07 is in columns A&B
and for 3/13/07 in D&E.

Let column F contain your 401 codes:

Then percentage change: =((VLOOKUP,F1,$D:$E,2,0)/VLOOKUP(F1,$A:$B,2,0))-1


Format cell as percentage


(Some of the % in your sample are wrong: % change = (new value - old
value)/old value)

HTH

"Zr2x350" wrote:



"Zr2x350" wrote:

here is an example of how its set up and what i would like for it to look
like....
date 3/13/2007
401k1 1000
401k2 1000
401k3 2000
401k4 3000
t0tal 7000

date 3/14/2007
401k1 2000
401k2 1000
401k3 2000
401k4 5000
total 10000

then in a box by itself I would like to be able to keep up with the daily
percentage drop/increase in each section
PERCENTAGE increase/decrease
401k1 100%
401k2 0%
401k3 o%
401k4 75%
total 120%

Can someone help me with this issue please. Thanks
"Zr2x350" wrote:

Ok, I have a 401k database set up for an entire month and I have been trying
to figure out how in the world to set each day up to when you post your new
earnings for the day, a new percentage would pop up showing how much you
have gained or lost in that certain fund for the month. I was wanting to do
a percentage for each fund as well as for the grand total of all of them.
Also when the total is 0 I wanted to omit those out of the percentage,
because you all know on the weekends nothing is posted. Also I just want it
at the bottom so it is listed only once for the whole month, it doesnt have
to be beside each individual day. Thanks a bunch.

taking percentages
 

Not sure how data is organised but assume data for 3/13/07 is in columns A&B
and for 3/13/07 in D&E.

Let column F contain your 401 codes:

Then percentage change: =((VLOOKUP(F1,$D:$E,2,0)/VLOOKUP(F1,$A:$B,2,0))-1

This matches data in F1 against data in column D and if it finds a match,
returns the value in column E (the 2 refers to the second column of the
specified 'lookup' range: in this case columns D & E).

Similarly, the second VLOOKUP matches F1 against column A and returns value
from B.

Change the columns A &B, C&D to suit your data plus column F. If the 401
codes are a column, copy the formula down in the adjacent column (or
whichever you are using).

Look at HELP on VLOOKUP function.




"Zr2x350" wrote:

Yeah I just wrote those percentages down for an example, i didnt actually
figure them I just wanted you guys to get the idea of the layout. So how
exactly does this VLOOKUP: work? you just type in the figures you want it to
work?

"Toppers" wrote:

Use VLOOKUP:

Not sure how data is organised but assume data for 3/13/07 is in columns A&B
and for 3/13/07 in D&E.

Let column F contain your 401 codes:

Then percentage change: =((VLOOKUP,F1,$D:$E,2,0)/VLOOKUP(F1,$A:$B,2,0))-1


Format cell as percentage


(Some of the % in your sample are wrong: % change = (new value - old
value)/old value)

HTH

"Zr2x350" wrote:



"Zr2x350" wrote:

here is an example of how its set up and what i would like for it to look
like....
date 3/13/2007
401k1 1000
401k2 1000
401k3 2000
401k4 3000
t0tal 7000

date 3/14/2007
401k1 2000
401k2 1000
401k3 2000
401k4 5000
total 10000

then in a box by itself I would like to be able to keep up with the daily
percentage drop/increase in each section
PERCENTAGE increase/decrease
401k1 100%
401k2 0%
401k3 o%
401k4 75%
total 120%

Can someone help me with this issue please. Thanks
"Zr2x350" wrote:

Ok, I have a 401k database set up for an entire month and I have been trying
to figure out how in the world to set each day up to when you post your new
earnings for the day, a new percentage would pop up showing how much you
have gained or lost in that certain fund for the month. I was wanting to do
a percentage for each fund as well as for the grand total of all of them.
Also when the total is 0 I wanted to omit those out of the percentage,
because you all know on the weekends nothing is posted. Also I just want it
at the bottom so it is listed only once for the whole month, it doesnt have
to be beside each individual day. Thanks a bunch.

taking percentages
 

Thanks a bunch!

"Toppers" wrote:

Not sure how data is organised but assume data for 3/13/07 is in columns A&B
and for 3/13/07 in D&E.

Let column F contain your 401 codes:

Then percentage change: =((VLOOKUP(F1,$D:$E,2,0)/VLOOKUP(F1,$A:$B,2,0))-1

This matches data in F1 against data in column D and if it finds a match,
returns the value in column E (the 2 refers to the second column of the
specified 'lookup' range: in this case columns D & E).

Similarly, the second VLOOKUP matches F1 against column A and returns value
from B.

Change the columns A &B, C&D to suit your data plus column F. If the 401
codes are a column, copy the formula down in the adjacent column (or
whichever you are using).

Look at HELP on VLOOKUP function.




"Zr2x350" wrote:

Yeah I just wrote those percentages down for an example, i didnt actually
figure them I just wanted you guys to get the idea of the layout. So how
exactly does this VLOOKUP: work? you just type in the figures you want it to
work?

"Toppers" wrote:

Use VLOOKUP:

Not sure how data is organised but assume data for 3/13/07 is in columns A&B
and for 3/13/07 in D&E.

Let column F contain your 401 codes:

Then percentage change: =((VLOOKUP,F1,$D:$E,2,0)/VLOOKUP(F1,$A:$B,2,0))-1


Format cell as percentage


(Some of the % in your sample are wrong: % change = (new value - old
value)/old value)

HTH

"Zr2x350" wrote:



"Zr2x350" wrote:

here is an example of how its set up and what i would like for it to look
like....
date 3/13/2007
401k1 1000
401k2 1000
401k3 2000
401k4 3000
t0tal 7000

date 3/14/2007
401k1 2000
401k2 1000
401k3 2000
401k4 5000
total 10000

then in a box by itself I would like to be able to keep up with the daily
percentage drop/increase in each section
PERCENTAGE increase/decrease
401k1 100%
401k2 0%
401k3 o%
401k4 75%
total 120%

Can someone help me with this issue please. Thanks
"Zr2x350" wrote:

Ok, I have a 401k database set up for an entire month and I have been trying
to figure out how in the world to set each day up to when you post your new
earnings for the day, a new percentage would pop up showing how much you
have gained or lost in that certain fund for the month. I was wanting to do
a percentage for each fund as well as for the grand total of all of them.
Also when the total is 0 I wanted to omit those out of the percentage,
because you all know on the weekends nothing is posted. Also I just want it
at the bottom so it is listed only once for the whole month, it doesnt have
to be beside each individual day. Thanks a bunch.