I have a spreadsheet that calculates investment returns continuing to calculate growth after retirement up to 100 years of age. (Not unthinkable for the youth of this day and age)
Start with this so you can see what I'm trying to do.
https://drive.google.com/open?id=0B01KC2Iq-_wKNjVCbm82N0JleWc
3rd sheet (Early Retirement) (Current sheet as you open it (Saved State)) Oops I guess saved state doesn't take online only if you download it.
What I would like to happen in cell M71 is I would like it to sum column M up to 100 years old (Column J)
I'm assuming I could use a vlookup for this but I'm not sure the syntax to use to use both a sum statement and a vlookup in the same formula.
I tried this =SUM(M10:VLOOKUP((100),$J$10:$N70,)
but that's basically all I've got. It doesn't work anyway because what I actually need to do is then take the number from column I (the prior column) and multiply it by "-$M$7" (The annual withdrawal) but as far as I know a lookup has to start with the column you are looking up and can only get values from columns to the right of that. Is there a way I can do the lookup on column J, get the value from column I and multiply it by cell "$M$7"
No that won't work either because that assumes the values in column M are all the same which works if the person's age and starting capital allow the investment to draw the full annual income "M7" to 100 years of age but you can see in the next table that is not always going to be the case so I'm back to a sum formula. I need the lookup to get the 100 from column J and then do a sum of column M up to and including the "Age 100 row" This would solve the problem of including the partial annual withdrawal. The reason I need to do it like this is because there's actually a bunch of places I would like to implement this functionality once it's perfected and I can't just sum the column up to 100 because the 100th year is going to change from rows depending on the age that activates this table. Change cell J9 to 65 to see what I mean. Then CTRL+Z to change it back. It's all linked to other functionality.
Thanks in advance for your help
Start with this so you can see what I'm trying to do.
https://drive.google.com/open?id=0B01KC2Iq-_wKNjVCbm82N0JleWc
3rd sheet (Early Retirement) (Current sheet as you open it (Saved State)) Oops I guess saved state doesn't take online only if you download it.
What I would like to happen in cell M71 is I would like it to sum column M up to 100 years old (Column J)
I'm assuming I could use a vlookup for this but I'm not sure the syntax to use to use both a sum statement and a vlookup in the same formula.
I tried this =SUM(M10:VLOOKUP((100),$J$10:$N70,)
but that's basically all I've got. It doesn't work anyway because what I actually need to do is then take the number from column I (the prior column) and multiply it by "-$M$7" (The annual withdrawal) but as far as I know a lookup has to start with the column you are looking up and can only get values from columns to the right of that. Is there a way I can do the lookup on column J, get the value from column I and multiply it by cell "$M$7"
No that won't work either because that assumes the values in column M are all the same which works if the person's age and starting capital allow the investment to draw the full annual income "M7" to 100 years of age but you can see in the next table that is not always going to be the case so I'm back to a sum formula. I need the lookup to get the 100 from column J and then do a sum of column M up to and including the "Age 100 row" This would solve the problem of including the partial annual withdrawal. The reason I need to do it like this is because there's actually a bunch of places I would like to implement this functionality once it's perfected and I can't just sum the column up to 100 because the 100th year is going to change from rows depending on the age that activates this table. Change cell J9 to 65 to see what I mean. Then CTRL+Z to change it back. It's all linked to other functionality.
Thanks in advance for your help
