Page 1 of 4 123 ... LastLast
Results 1 to 12 of 46

Thread: Excel Formula, I need help :(

  1. #1

    Join Date
    Jul 2007
    Location
    Europe
    Posts
    8,430
    Blog Entries
    297

    Default Excel Formula, I need help :(

    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, 55G/15C/15S/15I (Paper Trading)_ BLOG: Stats for March, Stats for Q1, 2024 Stats


  2.  
  3. #2

    Join Date
    Jun 2012
    Location
    Texas
    Posts
    1,640

    Default Re: Excel Formula, I need help :(

    Couldn't you just "hide" Column K?
    "The flag stands for rights, not results. Time to grow-up buttercup"

  4.  
  5. #3

    Join Date
    Jul 2007
    Location
    Europe
    Posts
    8,430
    Blog Entries
    297

    Default Re: Excel Formula, I need help :(

    Quote Originally Posted by PLANO View Post
    Couldn't you just "hide" Column K?
    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, 55G/15C/15S/15I (Paper Trading)_ BLOG: Stats for March, Stats for Q1, 2024 Stats

  6.  
  7. #4

    Join Date
    Jun 2012
    Location
    Texas
    Posts
    1,640

    Default Re: Excel Formula, I need help :(

    Quote Originally Posted by JTH View Post
    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 them data. Right now the file is 42megs and I still don't have everything I need.
    Wow...that's a huge spreadsheet! Sorry....after "can't you just hide it".....that's all I have on Excel. I'm no guru, but there's probably lots of people on here who are. I like what you're doing with it though, makes me want to do some tinkering myself, thanks!
    "The flag stands for rights, not results. Time to grow-up buttercup"

  8.  
  9. #5

    Join Date
    Feb 2011
    Location
    Washington
    Posts
    9,583

    Default Re: Excel Formula, I need help :(

    Quote Originally Posted by JTH View Post
    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.
    So, why doesn't =format(sum(a1/(sum(a1:a20)/20)-1),"0.000%") work?

    I added the format so that it shows in the right format no matter how the cell is formatted. You can leave it out if you want.
    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

  10.  
  11. #6

    Join Date
    Jul 2007
    Location
    Europe
    Posts
    8,430
    Blog Entries
    297

    Default Re: Excel Formula, I need help :(

    Quote Originally Posted by RealMoneyIssues View Post
    So, why doesn't =format(sum(a1/(sum(a1:a20)/20)-1),"0.000%") work?

    I added the format so that it shows in the right format no matter how the cell is formatted. You can leave it out if you want.
    Thanks, it didn't work, but you got me in the ballpark and it works now! I'm stoked, this just makes my day

    Thanks again...Happy Jason
    Retired, 55G/15C/15S/15I (Paper Trading)_ BLOG: Stats for March, Stats for Q1, 2024 Stats

  12.  
  13. #7

    Default Re: Excel Formula, I need help :(

    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

  14.  
  15. #8

    Join Date
    Dec 2007
    Location
    inland Northwest
    Posts
    4,124

    Default Re: Excel Formula, I need help :(

    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

  16.  
  17. #9

    Default Re: Excel Formula, I need help :(

    Quote Originally Posted by JTH View Post
    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.
    Why use the SUM function on a single number??
    What RMI posted would simply to =20*a1/sum(a1:a20)-1


  18.  
  19. #10

    Join Date
    Jul 2007
    Location
    Europe
    Posts
    8,430
    Blog Entries
    297

    Default Re: Excel Formula, I need help :(

    Quote Originally Posted by k0nkuzh0n View Post
    Why use the SUM function on a single number??
    What RMI posted would simply to =20*a1/sum(a1:a20)-1
    That formula didn't work (it came up with a a negative number), as to why use SUM, well you're asking the wrong person, I am not an excel person and even less of a math person
    Retired, 55G/15C/15S/15I (Paper Trading)_ BLOG: Stats for March, Stats for Q1, 2024 Stats

  20.  
  21. #11

    Join Date
    Feb 2011
    Location
    Washington
    Posts
    9,583

    Default Re: Excel Formula, I need help :(

    Quote Originally Posted by RealMoneyIssues View Post
    So, why doesn't =format(sum(a1/(sum(a1:a20)/20)-1),"0.000%") work?
    Not sure why it wouldn't work, but I also did it free-hand (not in Excel).

    Quote Originally Posted by k0nkuzh0n View Post
    Why use the SUM function on a single number??
    What RMI posted would simply to =20*a1/sum(a1:a20)-1
    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.

    Quote Originally Posted by JTH View Post
    That formula didn't work (it came up with a a negative number), as to why use SUM, well you're asking the wrong person, I am not an excel person and even less of a math person
    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

  22.  
  23. #12

    Default Re: Excel Formula, I need help :(

    Quote Originally Posted by k0nkuzh0n View Post
    Why use the SUM function on a single number??
    What RMI posted would simply to =20*a1/sum(a1:a20)-1
    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%

  24.  
Page 1 of 4 123 ... LastLast

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
S&P500 (C Fund) (delayed)
Excel Formula, I need help :(
(Stockcharts.com Real-time)
DWCPF (S Fund) (delayed)
Excel Formula, I need help :(
(Stockcharts.com Real-time)
EFA (I Fund) (delayed)
Excel Formula, I need help :(
(Stockcharts.com Real-time)
BND (F Fund) (delayed)
Excel Formula, I need help :(
(Stockcharts.com Real-time)

Yahoo Finance Realtime TSP Fund Tracking Index Quotes