• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Range formula that will return a average

C

comp764

Member
Hello,

I searching for days to find a formula for a dynamic chart that will return a moving average. I had this formulas working in excel 2000 but now that I'm upgraded to excel 2007 they no longer work. Here is the formula I was using before =('sheet1'!$A$1)*AVERAGE('sheet1'!$A$1) and this was given me a moving average, min, and max. But now it no longer work.


I have a dynamic chart and name ranges the formula I am using now for all my info is

=OFFSET(SHEET1!$A$1,MAX(0,COUNTA(SHEET1!$A:$A)-CHART!$D$1-1),0,CHART1!$D$1,1)

I am using this formula for all three columns that I getting all my info for my charts.


Is there any formula that I can use that will give me a moving average.


Thanks, Dan
 
Hi, comp764!


The formula "=('sheet1'!$A$1)*AVERAGE('sheet1'!$A$1)" (unquoted) does nothing but raising to the power of 2 the value in cell A1 of sheet sheet1, as the average of one only cell is its own value.


Can you please upload the file? If needed refer to the first three green sticky posts at this forums main page for guidelines.


Regards!
 
Hi, comp764!


Unless it is for corporate policies or for data privacy you can:

a) type www.hotfile.com in your web browser

b) register for free if first use or identify yourself

c) click on the gray button 'Select file'

d) select the file from the dialog

e) click on agreement and then click on gray button 'Upload'

f) copy the URL field and paste it here


If it's a problem about data privacy, just do as stated in the green topics, change data, copy values, and so on, but do not alter the workbook structure


Hope it helps you.


Regards!
 
Hi Sir,

Sorry I taking so long. Here is he file. What I would like to do is have a moving average in my chart. This is 4 chart of 76 chart I have to make. This is not the original file. I have ninteen worksheets, each with a different product on it. So for each product I have to show Ph, Visc, Temp, and Solids. So I have to show four charts on each sheet for each category

Any help would be greatly appreciated.


https://hotfile.com/dl/150443324/5f9e152/BatchSpecs.xlsx.html


Thanks
 
Hi, comp764!


I'll give a look to that file. But either my memory is playing tricks or it's a deja vu... I've read a few days ago about Ph, Visc, Temp, Solids, charts...


Gonna do a search before so as avoid working twice or reinventing the wheel.


Regards!
 
Hi, comp764!


I was right, it was in your previous post: http://chandoo.org/forums/topic/dynamic-range-linked-to-a-cell-return-error-message


Not every day I read about chemistry here.


Regards!
 
Hi Sir,

I'm sorry for posting this agian. But it killing me not being able to find the solution the this. If it can't be done that's fine, but just want to know from a excel expert that is can or can't be done. I'm not very good with excel. Everything I've done so far I learned from reading some books and a lot of research on the internet forums, like this one.


Thanks
 
Comp764


Can't be done ?

This bad boy will do the job for you for the Ph chart


Code: 
PhAv: =(ROW(OFFSET(Sheet1!$A$1,,,Sheet1Chart!$D$1,1))^0) * AVERAGE(OFFSET(Sheet1!$F$2, MAX(0, COUNTA(Sheet1!$F:$F) -Sheet1Chart!$D$1 - 1), 0, Sheet1Chart!$D$1, 1))


[code]Visc Av: =(ROW(OFFSET(Sheet1!$A$1,,,Sheet1Chart!$D$1,1))^0)* AVERAGE(OFFSET(Sheet1!$G$2, MAX(0, COUNTA(Sheet1!$G:$G)-Sheet1Chart!$D$1-1), 0, Sheet1Chart!$D$1, 1))


Sol Av: =(ROW(OFFSET(Sheet1!$A$1,,,Sheet1Chart!$D$1,1))^0)* AVERAGE(OFFSET(Sheet1!$H$2,MAX(0, COUNTA(Sheet1!$H:$H)-Sheet1Chart!$D$1-1), 0, Sheet1Chart!$D$1, 1))


TempAv: =(ROW(OFFSET(Sheet1!$A$1,,,Sheet1Chart!$D$1,1))^0) * AVERAGE(OFFSET(Sheet1!$E$2, MAX(0, COUNTA(Sheet1!$E:$E) -Sheet1Chart!$D$1-1), 0, Sheet1Chart!$D$1, 1))[/code]


They are to be used as a Named Formula and then added to each chart as a new series
 
Hi Hui,

Thanks for your response. I tried the formula and used in Name Manager , named it and typed into Refers to: but it tell me that the formula contains an error. After I click ok on the error message dialog box it's highlighting the zero in this part of the formula ))^0).


Also don't get the part of the formula that refers to sheet1!$A$1 that the header for the Date.


Thanks again for your help.
 
Hi Dan ,


Going by the time zone Hui is in ( Australia ) , I think he may reply only tomorrow , which is why I am taking the liberty of replying.


Your worksheet is at this link :


https://docs.google.com/spreadsheet/ccc?key=0AkKMpuzr3MTVdDEzWG4xdTZEMExLeGxxRVFVcllvVmc


Only the chart for Ph has been modified ; based on that , you can modify the remaining charts.


Narayan
 
Hi Narayan,

I just got to look at file. But when I click on the link it's in a different format. Not sure what I need to do to look at it in excel format.


Thanks, Dan
 
Hi Dan ,


Sorry about that. I had the Compatibility feature ON ; try this link :


https://docs.google.com/open?id=0B0KMpuzr3MTVRDJ4WWVCTzJRbGlTNDBlSVV0akxjdw


Narayan
 
Hi Narayan,

Got the worksheet! Formula work great! Exactly what I wanted. Thanks you very much Narayan and thank you Hui ! You guys have been a great help. I am sure I'll be coming be here when I need some more help!


Thanks again your help is greatly appreciated.


Dan
 
Comp764


Yes, I'm in Australia and enjoy my sleep


The formula Narayan used is exactly the same as mine and so I think you've done something incorrectly.


Don't worry about the Sheet1 bit, It is purely a place holder and doesn't use the cells value or contents at all.


When copying formulas from Chandoo.org there are times where you need to retype the characters. This is a common problem with formulas that include " characters and may also be the case with ^

Between the original Excel copy of the formula, Chandoo's web software and then Excel again, the text sometimes gets misinterpreted and replaced with a character that looks like a " or ^ but is in fact not the right character.
 
Hi Hui ,


Just a clarification - I did not use any formula of mine ; since I assumed you would not be in a position to respond to Dan's problem immediately , I took the liberty of copying your formulae from your post !


I merely inserted the formulae for the range names and configured the chart accordingly.


Dan had a problem with the uploaded workbook , because when uploading to Google Docs , I had the compatibility feature ON , and Google Docs was doing some conversion which was generating errors in the workbook ; this morning , after getting the feedback from Dan on this , I uploaded the workbook after switching OFF the conversion feature ; I think Dan has got everything correct now. Thanks.


Narayan
 
You must log in or register to reply here.
Back
Top