Hi, need help with understanding a formula which uses a combination of sumproduct and offset (available in the file attached). Whenever a new center is opened, it needs to start the count from 12. The existing centers' capacity is picked based on when they started. The formula is doing it correctly, but I am trying (very hard) to understand the logic. Any help will be appreciated. Thanks!
sumproduct + offset
- Thread starter AJN
- Start date
David Evans
Active Member
AJN -
Take a look at the attached file - I've broken down the formula for you into smaller bits so you can "see" what's happening. You might also want to use Excel's Evaluate Formula to "see" the calculation.
There is something about the Centers opened row that i find not totally intuitive - I think the value should be cumulative, but that would not work for the way the worksheet is currently structured. There is also an intersting use of "N" in the formula that I can safely say I've never seen before - thanks for teaching me something ...
Take a look at the attached file - I've broken down the formula for you into smaller bits so you can "see" what's happening. You might also want to use Excel's Evaluate Formula to "see" the calculation.
There is something about the Centers opened row that i find not totally intuitive - I think the value should be cumulative, but that would not work for the way the worksheet is currently structured. There is also an intersting use of "N" in the formula that I can safely say I've never seen before - thanks for teaching me something ...
Hi, Thanks! Breaking down the formula really helped and am glad you got something new out of this 
. Have a couple of questions still, though :
- Why are we using Cell("Col", X:X) instead of just Column(X:X) - in this formula it would just cancel out - but don't both these actually give the same result?
- How is using N helping the formula?
. Have a couple of questions still, though : - Why are we using Cell("Col", X:X) instead of just Column(X:X) - in this formula it would just cancel out - but don't both these actually give the same result?
- How is using N helping the formula?
David Evans
Active Member
Hi AJN -
Didn't have a lot of time to review your questions as I'm heading out for a day's golf ....
However, I added a few more lines to the Formula Breakdown area to see exactly what was happening.
On a cursory review of the results - I think you're right that the Col references appear to be cancelling each other out; but it needs a bit more testing.
The "N" *appears* to be behaving in a non-documented manner, by removing the #VALUE# from the resultant array and replacing it with 0. Perhaps @NARAYANK991 has seen this prior - as I mentioned, I've not witnessed it. The quick look i had of the N documentation did not suggest it had that effect on #VALUE# ... https://support.office.com/en-us/article/N-function-a624cad1-3635-4208-b54a-29733d1278c9
That suggests that #VALUE# should return #VALUE# but in this case it is not ...
Didn't have a lot of time to review your questions as I'm heading out for a day's golf ....
However, I added a few more lines to the Formula Breakdown area to see exactly what was happening.
On a cursory review of the results - I think you're right that the Col references appear to be cancelling each other out; but it needs a bit more testing.
The "N" *appears* to be behaving in a non-documented manner, by removing the #VALUE# from the resultant array and replacing it with 0. Perhaps @NARAYANK991 has seen this prior - as I mentioned, I've not witnessed it. The quick look i had of the N documentation did not suggest it had that effect on #VALUE# ... https://support.office.com/en-us/article/N-function-a624cad1-3635-4208-b54a-29733d1278c9
That suggests that #VALUE# should return #VALUE# but in this case it is not ...
Chihiro
Excel Ninja
- Why are we using Cell("Col", X:X) instead of just Column(X:X)...
When nested inside SUMPRODUCT(), COLUMN(Range) actually returns array of column index, where as if you use it alone it will evaluate to single value .
Cell("Col",Range) in this instance can be replaced with contant (2). Since starting column will always be 2.
Taking C7 for example...
=SUMPRODUCT($B$4:C$4,N(OFFSET($B5:C5,0,COLUMNS($B5:C5)-COLUMN($B5:C5)+CELL("Col",$B5:C5)-1)))
Becomes...
=SUMPRODUCT($B$4:C$4,N(OFFSET($B5:C5,0,2-{2,3}+2-1)))
=SUMPRODUCT($B$4:C$4,N(OFFSET($B5:C5,0,{1,0})))
=SUMPRODUCT({1,0},{18,12})
=18
- How is using N helping the formula?
Basically it's used to produce usable array from embedded array. In this case from OFFSET(Range,0,{Array}). Read through link below for the concept.
https://newtonexcelbach.wordpress.com/2013/01/01/using-excels-shortest-functions/
Edit: Actually formula can be shortened to below in this case.
=SUMPRODUCT($B$4:B$4,N(OFFSET($B$5,0,COLUMNS($B5:B5)-COLUMN($B5:B5)+1)))
When nested inside SUMPRODUCT(), COLUMN(Range) actually returns array of column index, where as if you use it alone it will evaluate to single value .
Cell("Col",Range) in this instance can be replaced with contant (2). Since starting column will always be 2.
Taking C7 for example...
=SUMPRODUCT($B$4:C$4,N(OFFSET($B5:C5,0,COLUMNS($B5:C5)-COLUMN($B5:C5)+CELL("Col",$B5:C5)-1)))
Becomes...
=SUMPRODUCT($B$4:C$4,N(OFFSET($B5:C5,0,2-{2,3}+2-1)))
=SUMPRODUCT($B$4:C$4,N(OFFSET($B5:C5,0,{1,0})))
=SUMPRODUCT({1,0},{18,12})
=18
- How is using N helping the formula?
Basically it's used to produce usable array from embedded array. In this case from OFFSET(Range,0,{Array}). Read through link below for the concept.
https://newtonexcelbach.wordpress.com/2013/01/01/using-excels-shortest-functions/
Edit: Actually formula can be shortened to below in this case.
=SUMPRODUCT($B$4:B$4,N(OFFSET($B$5,0,COLUMNS($B5:B5)-COLUMN($B5:B5)+1)))
Last edited: