Let's say I want the following equation:
excel copy.png
Where A1 and B1 are references to the value of the cells. Is this possible?
Of course I can make a column with the values of J, compute [@J]*$B$1 in the column next to it, and then sum that column, but that wouldn't be convenient.
Last edited by Stormbringer; 01-15-2015 at 01:08 PM.
Can you attach an actual book instead of a picture (i actually don't see the picture, just the error box with a red x)
Sounds like sumproduct could be used though..
Is this the actual equation, or is this a simplification (oversimplification) of the actual equation?
Assuming this is the actual equation, rearrange the summation:
sum(j=0 to n-1) (j*c)=c*sum(j=0 to n-1) (j) which is c times the sum of the first n-1 integers (since the j=0 term contributes 0 to the sum). Anecdotally attributed to Gauss, this is just the formula for the triangle numbers -- sum(j=1 to x) of x = x*(x+1)/2 : http://en.wikipedia.org/wiki/Triangular_number This approach will get rid of the summation operation and reduce the problem to simple function of n-1.
If this is a simplification of the actual equation, I would look to see if a similar formula exists for the summation. If not, then you can see about working it out using a summation notation/SUMPRODUCT() type function
Last edited by MrShorty; 01-14-2015 at 03:07 PM.
Originally Posted by shg
Agreed with MrShorty. This is just the sum of the integers from 1 to 1 less the value in A1, the result then multiplied by the value in B1. This can be written as:
=B1*SUMPRODUCT(ROW(INDIRECT("1:"&A1))-1)
Regards
@Jonmo1: Sorry, I don't know why it isn't working for you, but a proper sigma-notation is the most clear option I think.
@MrShorty: Thanks a lot, I had forgotten the Triangular Numbers. However, this won't help me a lot since it was indeed an oversimplification.
@XOR LX: Interesting, I didn't know the INDIRECT-formula yet. I tried to implement that, but I'm having a hard time.
I'll try to explain the actual problem then. I want to forecast future sales by analysing historical demand. The demand needs to be deseasonalized (seasonal patterns must be eliminated) by getting seasonal factors to compensate for each season. Please see my workbook.
I have two tables, one with the historical demand and some computations, and another where I want to use the following formula in the whole column Season["Si" SeasonalFactor]:
Season.png
"p" and "r" are given (in I3 and I4), "i" is in the K-column in [@SeasonNumber], and F is in the F-column.
I hope it's not too much effort for you all, thanks a lot in advance again!
P.S.: The numbers in the formula E-column Demand[LinearRegression](=18439+524*[@Period]) I obtained with the help of a (Linear) Regression by analysing D6:D13 in the Y range and B6:B13 in the X range. So I've manually typed the two numbers in the formula with the result of the analysis. However, it would be great if this formula could be automated! I don't think it's possible, but I someone has an idea - that would be fantastic!
Last edited by Stormbringer; 01-14-2015 at 08:46 PM.
With r=3, doesn't this formula evaluate to sum(F1,F5,F9)/3? If I'm following this correctly, isn't this just =average(F1,F5,F9)?
I'm not very good at the fancy array formulas a lot of these guys use (I personally prefer to use helper cells for these kinds of things. It seems to me that someone should be able to work something out with the index function
=average(index($F$4:$F$24,{1,5,9})) entered as an array function. Then figure out how to get replace the array constant {1,5,9} with an appropriate formula.
AS for the regression question, it should be easily obtained using the LINEST() function. https://support.office.com/en-us/art...rs=en-US&ad=US
Unfortunately INDEX is not so obliging when passed an array to the row_num or column_num parameter, and requires a little extra coercion:
Your formula would resolve to just:
AVERAGE($F$4)
whether array-entered or not.
The required coercion is:
=AVERAGE(INDEX($F$4:$F$24,N(IF(1,{1,5,9}))))
and does not require CSE.
More if you're interested:
http://excelxor.com/2014/09/05/index...ray-of-values/
Regards
Not sure I understand. This new summation formula bears only a passing resemblance to that in your original post. The solution I provided was equivalent to that original construction.
Regards
You're right, it's indeed the average of those cells. And thanks a lot for the LINEST function, works great!
Thanks! Nice article too! However, I'd like to have those "j*p+i" implemented automatically, and not fill in {1,5,9} manually. Is this possible? If not, I'll use your solution in combination with a some other cells to get {1,5,9}, {2,6,10}, {3,7,11}, etc.
I know, and thank you. I just hoped there would be a more straightforward solution like "sum(from,to,summation)" and I could use that like "sum(j=0,j=A1-1,j*B1)" for my general summation in the first post. In that case the actual equation I need would have been easy to use too.
No problem. So just to confirm, do you want a formula that, when copied down (or is it across?), is equivalent to:
=AVERAGE(INDEX($F$4:$F$24,N(IF(1,{1,5,9}))))
in the first such cell, and:
=AVERAGE(INDEX($F$4:$F$24,N(IF(1,{2,6,10}))))
in the second such cell, and:
=AVERAGE(INDEX($F$4:$F$24,N(IF(1,{3,7,11}))))
in the third such cell, etc., etc.?
Regards
Copied down indeed! Where i from "j*p+i" is the value of SeasonNumber in the K-column. Thanks!
Er, not sure what that bit means! Would you be happy with exactly as I gave, from which you can then adapt to suit your needs? If not, please explain!
Regards
Sorry! {1,5,9} (and the others) are derived from j*p+i in the summation I gave earlier.
Season.png
"p" = cell I3 in the Excel file I gave, and "i" = [@SessionNumber] (or cell K4) for the first term. "j" starts from 0 to "r-1" where "r" = cell I4.
In the particular file, p=4, and r=3.
In other words, the first set is j(from 0 to r-1)*p+i = j(from 0 to 3-1)*4+1 = {0*4+1,1*4+1,2*4+1} = {1,5,9}
In the cell below "i" changes to 2 (=cell K5) and we get {0*4+2,1*4+2,2*4+2} = {2,6,10}
So what I'd love to have, is that this "j*p+i" would be automatically applied within your formula depending on "p" and "r". So if I change "p" to 2 and "r" to 6, we would get:
=AVERAGE(INDEX($F$4:$F$24,N(IF(1,{j(from 0 to r-1)*p+i}))))
where "j(from 0 to r-1)*p+i" refers to j(from 0 to $I$4-1)*$I$3+[@SeasonNumber], which should become for cell M4 (first entry) j(from 0 to 6-1)*2+1 = {0*2+1,1*2+1,2*2+1,3*2+1,4*2+1,5*2+1} = {1,3,5,7,9,11}.
In combination with your AVERAGE formula this would become 0.842 for the first cell.
So again, I hope there's a way to get {1,5,9} or {1,3,5,7,9,11} or whatever within your formula automatically, depending on the values of "p" and "r". Thanks again for the great help!
Thanks.
I'm afraid I can't follow your cell references, though perhaps if I give you this array formula**:
=AVERAGE(INDEX($F$4:$F$24,N(IF(1,1+A1*(ROW(INDIRECT("1:"&A2))-1)))))
which is based on "p" and "r" being in A1 and A2 respectively, and which is equivalent to:
=AVERAGE(INDEX($F$4:$F$24,{1;3;5;7;9;11}))
when "p"=2 and "r"=6
and:
=AVERAGE(INDEX($F$4:$F$24,{1;5;9}))
when "p"=4 and "r"=3, etc.
then perhaps you can take it from there?
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Got it, enormous thanks! Very handy combinations!
You're welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks