Excel Help with calculating averages with custom input - Psychology and Money Management | futures io social day trading
futures io futures trading


Excel Help with calculating averages with custom input
Updated: Views / Replies:1,407 / 15
Created: by RM99 Attachments:2

Welcome to futures io.

(If you already have an account, login at the top of the page)

futures io is the largest futures trading community on the planet, with over 90,000 members. At futures io, our goal has always been and always will be to create a friendly, positive, forward-thinking community where members can openly share and discuss everything the world of trading has to offer. The community is one of the friendliest you will find on any subject, with members going out of their way to help others. Some of the primary differences between futures io and other trading sites revolve around the standards of our community. Those standards include a code of conduct for our members, as well as extremely high standards that govern which partners we do business with, and which products or services we recommend to our members.

At futures io, our focus is on quality education. No hype, gimmicks, or secret sauce. The truth is: trading is hard. To succeed, you need to surround yourself with the right support system, educational content, and trading mentors Ė all of which you can find on futures io, utilizing our social trading environment.

With futures io, you can find honest trading reviews on brokers, trading rooms, indicator packages, trading strategies, and much more. Our trading review process is highly moderated to ensure that only genuine users are allowed, so you donít need to worry about fake reviews.

We are fundamentally different than most other trading sites:
  • We are here to help. Just let us know what you need.
  • We work extremely hard to keep things positive in our community.
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts.
  • We firmly believe in and encourage sharing. The holy grail is within you, we can help you find it.
  • We expect our members to participate and become a part of the community. Help yourself by helping others.

You'll need to register in order to view the content of the threads and start contributing to our community.  It's free and simple.

-- Big Mike, Site Administrator

Reply
 2  
 
Thread Tools Search this Thread
 

Excel Help with calculating averages with custom input

  #1 (permalink)
Fortitudo et Honor
Austin, TX
 
Futures Experience: Advanced
Platform: TradeStation
Favorite Futures: Futures
 
Posts: 882 since Mar 2011
Thanks: 128 given, 703 received

Excel Help with calculating averages with custom input

I'm trying to take a column of values and calculate a simple moving average.

So if the first column is the values, and I want to calculate the 20 MA, then I go to the 20th value and in Column B I put "=SUM(A1:A20)/20"

I then copy and paste this all the way down to the end of the column to get the 20 MA values for the data.

This is probably a crude approach.

How do I craft it so that the MA setting is a reference cell that I can place different inputs?

Essentially, how do I use the reference cell to complete the row portion of the SUM() function?

"A dumb man never learns. A smart man learns from his own failure and success. But a wise man learns from the failure and success of others."
Reply With Quote
 
  #2 (permalink)
Quick Summary
Quick Summary Post

Quick Summary is created and edited by users like you... Add FAQ's, Links and other Relevant Information by clicking the edit button in the lower right hand corner of this message.

 
  #3 (permalink)
Elite Member
Milan (I)
 
Futures Experience: Advanced
Platform: Ninjatrader
Broker/Data: Kinetick
Favorite Futures: FDAX,6E,CL,YM,NQ,ES
 
redratsal's Avatar
 
Posts: 1,653 since Oct 2010
Thanks: 1,217 given, 2,068 received


Instead of building the formulas try Technical Indicators with TA-Lib and, 89 TA indicators excel addins and it's free.

Reply With Quote
The following user says Thank You to redratsal for this post:
 
  #4 (permalink)
Fortitudo et Honor
Austin, TX
 
Futures Experience: Advanced
Platform: TradeStation
Favorite Futures: Futures
 
Posts: 882 since Mar 2011
Thanks: 128 given, 703 received


redratsal View Post
Instead of building the formulas try Technical Indicators with TA-Lib and, 89 TA indicators excel addins and it's free.

Thanks, I'll check it out. I'm using Tradestation so I'm not sure if that'll work.

Aside from that, I'm not averaging from the chart, but outputs on the performance report for trade information. I have to manually modify excel printouts, because referencing trade information in Easy Language is a mystery to me.

"A dumb man never learns. A smart man learns from his own failure and success. But a wise man learns from the failure and success of others."
Reply With Quote
 
  #5 (permalink)
Elite Member
las vegas
 
Futures Experience: Intermediate
Platform: Sierra Chart
Broker/Data: Velocity/IB
Favorite Futures: 6E
 
Posts: 1,147 since Feb 2010
Thanks: 304 given, 836 received

Assuming data starting in E1, average calculation starting in F1, and parameter in G1, formula compatible with all versions would be

=IF(ISERROR(AVERAGE(INDIRECT(("E"&ROW(E1)-$G$1+1)):E1)), "",AVERAGE(INDIRECT(("E"&ROW(E1)-$G$1+1)):E1))

Please register on futures.io to view futures trading content such as post attachment(s), image(s), and screenshot(s).


Can also use IFERROR on newer versions.

Reply With Quote
The following 4 users say Thank You to vegasfoster for this post:
 
  #6 (permalink)
Fortitudo et Honor
Austin, TX
 
Futures Experience: Advanced
Platform: TradeStation
Favorite Futures: Futures
 
Posts: 882 since Mar 2011
Thanks: 128 given, 703 received


vegasfoster View Post
Assuming data starting in E1, average calculation starting in F1, and parameter in G1, formula compatible with all versions would be

=IF(ISERROR(AVERAGE(INDIRECT(("E"&ROW(E1)-$G$1+1)):E1)), "",AVERAGE(INDIRECT(("E"&ROW(E1)-$G$1+1)):E1))

Please register on futures.io to view futures trading content such as post attachment(s), image(s), and screenshot(s).


Can also use IFERROR on newer versions.

You're awesome. Thanks...I'm running into all sorts of problems. My server doesn't have Excel, so I have to export in .mht format, which includes all the other data in the spreadsheet. So copying and pasting is a challenge to begin. THEN, if that weren't enough, I have to try to pull out data that skips rows, and I haven't been able to figure out how to paste with it skipping the blank rows. I click on paste special and check the block that indicates skip blanks, but it still pastes.

"A dumb man never learns. A smart man learns from his own failure and success. But a wise man learns from the failure and success of others."
Reply With Quote
 
  #7 (permalink)
Fortitudo et Honor
Austin, TX
 
Futures Experience: Advanced
Platform: TradeStation
Favorite Futures: Futures
 
Posts: 882 since Mar 2011
Thanks: 128 given, 703 received

Vegas,

Many thanks for the help. The forumula is working...partially. It's working fine to adjust the start based off the MA input (if I input 5, it starts filling values at the 5th row, if I put in 20, it starts filling values at row 20) however, the calculations are not outputting proper averages. I'm scratching my head....because it's verbatim what you posted, and yours is calculating fine.

"A dumb man never learns. A smart man learns from his own failure and success. But a wise man learns from the failure and success of others."
Reply With Quote
 
  #8 (permalink)
Elite Member
las vegas
 
Futures Experience: Intermediate
Platform: Sierra Chart
Broker/Data: Velocity/IB
Favorite Futures: 6E
 
Posts: 1,147 since Feb 2010
Thanks: 304 given, 836 received

I may not be understanding what you are trying to do, because if you enter 20 then why you would want to calculate on anything less than 20? Apart from that, I double checked that the averages are calculating correctly on mine, so can you post your file so I can look at it?

Reply With Quote
 
  #9 (permalink)
Fortitudo et Honor
Austin, TX
 
Futures Experience: Advanced
Platform: TradeStation
Favorite Futures: Futures
 
Posts: 882 since Mar 2011
Thanks: 128 given, 703 received

Please register on futures.io to view futures trading content such as post attachment(s), image(s), and screenshot(s).

"A dumb man never learns. A smart man learns from his own failure and success. But a wise man learns from the failure and success of others."
Reply With Quote
 
  #10 (permalink)
Fortitudo et Honor
Austin, TX
 
Futures Experience: Advanced
Platform: TradeStation
Favorite Futures: Futures
 
Posts: 882 since Mar 2011
Thanks: 128 given, 703 received


Not sure why the row and column markers didn't come through, but the status bar is hilighted on cell B5 (the first value in the 2nd Column...column B).

I've scratched my head, because I can see the IF (logic) operator and I'm pretty sure I'm understanding the convention logic...it's saying..."IF the value is null, then output "" (blank)" if the value is not null, then output the average of ......Column A, then it calculates the start row, and the end row.

But I can't even reverse engineer where it's getting the value. I've tried to see if it's averaging the wrong set of values, if it's cutting the average sample short, or adding extra...etc.

I'm really scratching my head.......but the convention is working swimmingly, if I put 20 into the input cell, it begins populating values at B20 (and leaves B1 through B19 blank) it's just giving me the wrong output values.

"A dumb man never learns. A smart man learns from his own failure and success. But a wise man learns from the failure and success of others."
Reply With Quote

Reply



futures io > > > Excel Help with calculating averages with custom input

Thread Tools Search this Thread
Search this Thread:

Advanced Search



Upcoming Webinars and Events (4:30PM ET unless noted)

Jigsaw Trading: TBA

Elite only

FuturesTrader71: TBA

Elite only

NinjaTrader: TBA

Jan 18

RandBots: TBA

Jan 23

GFF Brokers & CME Group: Futures & Bitcoin

Elite only

Adam Grimes: TBA

Elite only

Ran Aroussi: TBA

Elite only
     

Similar Threads
Thread Thread Starter Forum Replies Last Post
Bill Williams Moving Averages. Rambo1123 NinjaTrader 15 June 21st, 2015 07:07 AM
Moving Averages - How do you use them? Fat Tails The Elite Circle 152 October 1st, 2012 05:23 PM
The Big Money's Moving Averages RODLM The Elite Circle 1 June 6th, 2010 04:29 PM
Moving averages yiman Traders Hideout 9 January 28th, 2010 11:19 AM
Indicator For Area Between Two Moving Averages adrian Traders Hideout 14 October 6th, 2009 05:49 PM


All times are GMT -4. The time now is 05:47 PM.

Copyright © 2017 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, +507 833-9432, info@futures.io
All information is for educational use only and is not investment advice.
There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
no new posts
Page generated 2017-12-17 in 0.13 seconds with 20 queries on phoenix via your IP 54.163.210.170