Excel Formula, I need help :(

[JTH]

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

 

[RealMoneyIssues]

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.

 

[PLANO]

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!

 

[JTH]

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.

 

[PLANO]

Couldn't you just "hide" Column K?

 

[JTH]

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.

View attachment 23627