Couldn't you just "hide" Column K?
Tom (anyone) I need some help
My Excel spreadsheets are getting a bit too bogged down, so I'm trying to consolidate some formulas, but I'm stuck on one I can't find the solution to.
As an example, right now I'm using 1 empty cell in Column K to calculate a 20SMA using closing prices located in Column A, Ex: =SUM(A1:A20)/20 this gets me the 20SMA
Then I use another empty cell in Column J to calculate the percentage of the close above/below the moving average from Columns A & J, Ex =SUM(A1/K1-1) this gets me the percentage of price above/below the 20SMA.
I do this over the 20, 50, 200 & 500 SMAs to to measure where we stand within current market conditions as compared to past historical market conditions.
Here is an example of the current setup, what I'd like to do is consolidate the 2 blue columns into 1, but I just want the percentage to be displayed and would like to not need Column K at all.
Attachment 23627
Retired, 10G/90C_ BLOG: Stats for April, 2024 Stats
Couldn't you just "hide" Column K?
"The flag stands for rights, not results. Time to grow-up buttercup"
Yes, (thank you for asking) that is what I normally do, but the more I can consolidate, the better excel will run, between the 32bit excel and my Lap Top, I am breaking the files and having to constantly rebuild them because it's getting corrupted. In case anyone ask "Yes I have reduced many formulas by pasting as text once they are no longer needed." But this isn't just 1 sheet we are talking about, and the charts suck up a lot of resources when you tie them to data. Right now the file is 42megs and I still don't have everything I need.
Retired, 10G/90C_ BLOG: Stats for April, 2024 Stats
"The flag stands for rights, not results. Time to grow-up buttercup"
Rules:
- Trade what you see, not what you believe
- Don't put stuff in your signature that a Mod doesn't like
"Government exists to protect all people’s rights, not some people’s feelings." - A. Barton Hinkle
Great Tools:
http://www.CreditKarma.com
http://www.Mint.com
http://www.SaveUp.com/r/nmJ
the answer is in the matrix. actually, msexcel calls it an array. i found out about them on here, when i was trying to build a conference calculator for best meeting spot out of 4 or 5 destinations coming from 40+ originations. after nesting formulas (up to 7 in one cell i think excel allows) into working columns where the answers were then aggregated and then worked again with another series of if/thens i gave up.
array is the hot ticket to massage a big table of data. you can even imbed array f(x)'s within another array, ie: 'pull this from there' and 'pull that from there' and do this to both of them. i think that is what you are after, in general. i'll give some thought to the specifics later.
the answer is already here a few years ago, but i'm not good with the search feature.
100g
Oh man, I had some math class many many moons ago that tried to teach me manual calcs from arrays-never did get the hang of them. giving me flashbacks. Maybe I could be more successful with Excel these days, but so far haven't got the motivation. kudoes to those who do.
"life can only be understood backwards, but it must be lived forwards" - soren kierkegaard
Not sure why it wouldn't work, but I also did it free-hand (not in Excel).
The sum function at sum(a1/k1-1) is not required, but it doesn't make it wrong either.
Your simplification is mathematically correct, although looking at it you wouldn't know what you are trying to do in the equation.
Excel doesn't care which way it is written:
=20*a1/sum(a1:a20)-1
or
=a1/(sum(a1:a20)/20)-1 (even with the extra parenthesis, just wanted to be clear what was calculated first) =a1/sum(a1:a20)/20-1 is equivalent
But if you were trying to figure out what the heck is going on years later, it might help to leave it in the original form so that it makes it easier to understand.
You said you got it to work, so it would be nice to know what did work...
Rules:
- Trade what you see, not what you believe
- Don't put stuff in your signature that a Mod doesn't like
"Government exists to protect all people’s rights, not some people’s feelings." - A. Barton Hinkle
Great Tools:
http://www.CreditKarma.com
http://www.Mint.com
http://www.SaveUp.com/r/nmJ
Strange that it didn't work for you, Jason. I just plugged it in and came up with the same value.
As for me, I would have written it as: =(A1/AVERAGE(A1:A20))-1
That also produces the same value. The outer braces aren't really needed here. I just include them to make it more readable.
Allocations as of COB Dec 28 : 100% S. | Retirement Date:Dec 2025
Past Returns: 2020 31.85%,2019 27.97%,2018 -3.36%,2017 13.10%, 2016 -1.79%, 5Yr Avg 12.61%
S&P500 (C Fund) (delayed) (Stockcharts.com Real-time) |
DWCPF (S Fund) (delayed) (Stockcharts.com Real-time) |
EFA (I Fund) (delayed) (Stockcharts.com Real-time) |
BND (F Fund) (delayed) (Stockcharts.com Real-time) |
||
Yahoo Finance Realtime TSP Fund Tracking Index Quotes |
Bookmarks