• 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.

Cell that formulates equation every day with data from prior 30 days

J

jeffm

New Member
Hello,

I've attached my reference material. If you go to the "NP CLK Data" sheet you will see my main data page. The rightmost column is a "Helper" column which I use to create dynamic charts which display the last 7 days of data.

What I am looking to do is utilize this column to create another column which performs a calculation each day which utilizes all of the data from the past 30 days.

The calculation itself is:
8ysapM7.png

Where: n = # of samples
xi = % C3S (this is one of the columns in the file I uploaded)
Ti = target C3S (55)

So the equation means 1 divided by the number of samples multiplied by the sum of the C3S of each sample - 55 all squared. It's a bit similar to a standard deviation calculation. The number of samples being the number included from 0 - 29 in the helper column as it begins at 0 and not 1.

Any help would be greatly appreciated.
 

Attachments

@jeffm,

Please check the attached workbook. I have taken Ti to be a constant value of 55 as indicated above although I'm not sure if it should be a variable.

The approach was to use SUMPRODUCT to return both the summation term as well as the sample count.

Hope that helps.

Regards,
Ken
 

Attachments

Ken,

Thanks for the help. It looks like the equation might be off somewhere. I just did a calculation on the last day that is in there (helper day 0) of which your equation came up with 77.4.

n = 5 therefore 1/n = .2
(53.82-55)^2 = 1.3924
(50.94 - 55)^2= 16.4836
(53.55 - 55)^2 = 2.1023
(55-55)^2 = 0
(51.59-55)^2 = 11.6281

The sum of these is 31.6066.

31.6066 * 0.2 = 6.32132

I wish I knew enough about what you have in the cell to offer input but it looks like a different language to me haha. Thanks again.
 
@jeffm,

Perhaps I misunderstood the intent of the number of samples; I took it to be the total number over the 30 day period. I have added a column which changes the formula to calculate a single day which agrees with your 6.32 value. In doing so, I did discover that there were some missing entries which would result in (0 - 55)^2 for the summation that were drastically altering the results.

So, I updated the formula to neglect the blanks (to be clear I have eliminated zeros, not strictly blanks) and only count actual samples. There are now two columns representing a 30 day sampling and a single day sampling (i.e., changed the helper column criteria from "<30" to "<1"). The revised version is attached.

Essentially the formula provides a conditional sum of the (C3S - 55)^2 term based on some additional criteria which establish the date range (based on helper column) and now the elimination of zeros.

The SUMPRODUCT formula is quite powerful and deserves more explanation than I can offer here. You might search this site for more info as well as http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

Sorry for the confusion.

Hope that helps.

Regards,
Ken
 

Attachments

Hi Ken,

Actually you were right in what I meant the first time I just completely forgot what I wanted for some reason. I guess I just expected lower numbers and that explanation came to mind albeit without enough thought.

With that being said, these numbers look EXACTLY like what I was expecting. Thank you so very much - it's perfect. Cheers.
 
You must log in or register to reply here.
Back
Top