Page 2 of 4 FirstFirst 1234 LastLast
Results 13 to 24 of 46

Thread: Excel Formula, I need help :(

  1. #13

    Default Re: Excel Formula, I need help :(

    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
    Cactus makes a great point to just use average. What is it a percent of?
    Percent above sma would be 1-A1/AVERAGE(A1:A20).
    Percent below would be A1/AVERAGE(A1:A20)-1.
    or maybe u want to flip it and do sma is x percent above: AVERAGE(A1:A20)/A1-1
    or sma is x percent below: 1-AVERAGE(A1:A20)/A1

    remember percent change is (previous-new)/previous. Which is ( previous/previous - new/previous). Which is ( 1 - new/previous)

  2.  
  3. #14

    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
    You said you got it to work, so it would be nice to know what did work...
    Where E is the closing prices, =SUM(E2/(SUM(E2:E21)/20)-1)
    Retired, 55G/15C/15S/15I (Paper Trading)_ BLOG: Stats for March, Stats for Q1, 2024 Stats

  4.  
  5. #15

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

    Default Re: Excel Formula, I need help :(

    Quote Originally Posted by Cactus View Post
    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.
    I tried it again, it worked this time, I must have done something wrong the first time, just spending too many hours staring at the monitor...
    Retired, 55G/15C/15S/15I (Paper Trading)_ BLOG: Stats for March, Stats for Q1, 2024 Stats

  6.  
  7. #16

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

    Default Re: Excel Formula, I need help :(

    I must say you folks are making me jealous if I had your excel skills I'd rule the world!

    I spent last night trying to get another formula to work and couldn't figure out out to consolidate it.

    Trying to figure out at what percentage does the closing price sit within it's 64 day MAX and MIN, currently I have to use 3 columns to do this. D is the close, C is the High, D is the low price. I have the text of the formula written in ROW 3, the output in Z

    Attachment 23633
    Retired, 55G/15C/15S/15I (Paper Trading)_ BLOG: Stats for March, Stats for Q1, 2024 Stats

  8.  
  9. #17

    Join Date
    Feb 2011
    Location
    Washington
    Posts
    9,583

    Default Re: Excel Formula, I need help :(

    um, guess:

    =(e2-min(c2:c65))/(max(c2:c65)-min(c2:c65))*100
    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. #18

    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
    um, guess:

    =(e2-min(c2:c65))/(max(c2:c65)-min(c2:c65))*100
    You're guess are better than mine, it worked!!!! (Doing the happy dance)
    Retired, 55G/15C/15S/15I (Paper Trading)_ BLOG: Stats for March, Stats for Q1, 2024 Stats


  12.  
  13. #19

    Join Date
    Aug 2008
    Location
    Pensacola, FL
    Posts
    6,312

    Default Re: Excel Formula, I need help :(

    I need an aspirin.

  14.  
  15. #20

    Join Date
    Jan 2011
    Location
    El Paso Texas
    Posts
    11,562

    Default Re: Excel Formula, I need help :(

    Quote Originally Posted by jpcavin View Post
    I need an aspirin.
    Excedrin for excel.
    Don't bias your charts. Show support and resistance. My comments and charts are not trading recommendations.

  16.  
  17. #21

    Join Date
    Feb 2011
    Location
    Washington
    Posts
    9,583

    Default Re: Excel Formula, I need help :(

    Sounds like a pretty interesting spreadsheet...

    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

  18.  
  19. #22

    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
    Sounds like a pretty interesting spreadsheet...

    Thanks everyone if you knew how much time I spend in excel, then you know how much this means to me, I now get to spend the weekend working on new spreadsheets, more work upfront, but a lot less work down the road, and it should run much cleaner.
    Retired, 55G/15C/15S/15I (Paper Trading)_ BLOG: Stats for March, Stats for Q1, 2024 Stats

  20.  
  21. #23

    Join Date
    Aug 2008
    Location
    Pensacola, FL
    Posts
    6,312

    Default Re: Excel Formula, I need help :(

    Quote Originally Posted by JTH View Post
    Thanks everyone if you knew how much time I spend in excel, then you know how much this means to me, I now get to spend the weekend working on new spreadsheets, more work upfront, but a lot less work down the road, and it should run much cleaner.
    Will you be sharing the results of your project? I think it's safe to assume we're all intrigued.

  22.  
  23. #24

    Default Re: Excel Formula, I need help :(

    Quote Originally Posted by JTH View Post
    Thanks everyone if you knew how much time I spend in excel, then you know how much this means to me, I now get to spend the weekend working on new spreadsheets, more work upfront, but a lot less work down the road, and it should run much cleaner.
    So were you able to make your spreadsheet less resource intensive? Is it still taking 42M?
    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 2 of 4 FirstFirst 1234 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