Unknown Error in Array Formula Using Sum()

deep_shobhit

New Member
Joined
Oct 6, 2006
Messages
3
Hi,

I'm getting an error while using the formula given below in Excel. The version I'm using is Excel 2002 SP3.

Code:
=SUM(IF(MOD(1,(COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-1))))- (COLUMN(INDIRECT(ADDRESS(ROW(),2))))+ 1- (ABS(INT((COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-1)))-COLUMN(INDIRECT(ADDRESS(ROW(),2)))+1)/3-0.1))*3))=0,INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-1)),0))

After giving the error, the first ROW() after teh ABS function is highlighted by Excel. However; If I'm selecting the formula from IF condition i.e. parameter to the SUM() and pressing F9 to evaluate, the values are coming fine. When I'm using SUM(), only then it is giving me the problem.

What I'm trying to do exactly from this formula is:

I have some values in the row in the set of 3, i.e. Questions Given, Attempted and Correct. I want to display the total of these three values in the last 3 cells.
So its like

---------------Question 1---Question 2---Question 3------Total
Student 1---1---1---0------1---1---0------1---1---0------3---3---0
Student 2---1---0---0------0---0---0------1---1---1------2---1---1
Student 3---1---1---1------1---0---0------1---1---1------3---2---2

The figures in the blue above are what I'm trying to derive. So, I want this formula to be of a generic kind of formula as the 3 Set Columns in the above data (for questions) are variable.

So, if you want to replicate the same problem on your system, enter following data in different columns in a worksheet starting from first column in any row:

0 -- 1 -- 2 -- 3 -- 1 -- 2 -- 3 -- 1 -- 2 -- 3 and the formula in the next cell.

Ideally, this formula should give the results as 3 (i.e. sum of figures in the positions having 1s or we can say every third position)

Any help for this will be highly appreacited. As I'm new to array formulas, I have already spent around 6 hours on this thing.
Thanks in advance

Regards
Shobhit

I have added -s in the codes/example to give worksheet like structure to the data. Please consider the -s as delimiter.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I think you've hit the maximum of 7 nested levels of functions.

What's wrong with?

=SUM(B2,E2,H2)

in K2 copied down and across?
 

deep_shobhit

New Member
Joined
Oct 6, 2006
Messages
3
Looks like that only....

Thanks Andrew,

Actually, I'm generating the XML for the report from my ASP.NET pages, and rendering that report data to Excel through XSLT. Now the number of questions in my case are not fixed. These can be anything.

Therefore I was trying to derive a generic function which can work in all conditions.

Looks like the same thing as you have suggested, max 7 level of functions...

Can I club some thing together so that this thing can work?

Thanks for your help...

Regards
Shobhit
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This does the job:

=SUMPRODUCT(--(MOD(COLUMN($B$1:$J$1),3)=MOD(COLUMN(),3)),$B2:$J2)

But there is a constant 3 in the formula so it's not truly generic.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Hi,

This is an inefficiant formula but I believe it replicates more what you were after above:

=SUMPRODUCT(--(MOD(COLUMN(INDEX(INDIRECT("A:IV"),ROW(),2):INDEX(INDIRECT("A:IV"),ROW(),COLUMN()-1))-2,3)=0),INDEX(INDIRECT("A:IV"),ROW(),2):INDEX(INDIRECT("A:IV"),ROW(),COLUMN()-1))
 

deep_shobhit

New Member
Joined
Oct 6, 2006
Messages
3
Thanks Andrew, Fairwinds,

The problem with the formula I have posted was exactly the same thing like suggested by Andrew, 7 nested function limit. However; I figured out that the ABS() was not required at all. I removed that and it worked. So eventually I have three formulas now which works for three digits in the above case.

For first summary calculated field:
Code:
=SUM(IF(MOD(1,(COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-1))))- (COLUMN(INDIRECT(ADDRESS(ROW(),2))))+ 1- (INT((COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-1)))-COLUMN(INDIRECT(ADDRESS(ROW(),2)))+1)/3-0.1))*3)=0,INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-1)),0))
For second Summary calculated field:
Code:
=SUM(IF(MOD(3,(COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-2))))- (COLUMN(INDIRECT(ADDRESS(ROW(),2))))+ 1- (INT((COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-2)))-COLUMN(INDIRECT(ADDRESS(ROW(),2)))+1)/3-0.1))*3)=1,INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-2)),0))
and finally for third Summary calculated field:
Code:
=SUM(IF(MOD(4,(COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-3))))- (COLUMN(INDIRECT(ADDRESS(ROW(),2))))+ 1- (INT((COLUMN(INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-3)))-COLUMN(INDIRECT(ADDRESS(ROW(),2)))+1)/3-0.1))*3)=1,INDIRECT(ADDRESS(ROW(),2) & ":" & ADDRESS(ROW(), COLUMN()-3)),0))

The formulas for three different fields are different but the only changes are the index numbers and the first parameter of the MOD().

I also tried using SUMPRODUCT, but got a little confused. This function gives the product of two arrays, whereas I wanted the sum total of the ranges. Will look more into this function as I hope this might be more easier to accomplish the same task if it can be used in this case.

Actually, It would have been much easier if I would have used custom function but again, in that case I can't use the code in XML Based Excel Sheet. It does not allows you to add custom code while saving the excel sheet as xml workbook.

Thanks a ton for the help. Please let me know if I can make it better.

Regards
Shobhit
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
I also tried using SUMPRODUCT, but got a little confused. This function gives the product of two arrays, whereas I wanted the sum total of the ranges.


SUMPRODUCT does as you describe. However in this case, whether you use Andrew's or my formula, it will multiply one array with result of the conditions. E.g. {1,0,0,1,0,0,1,0,0} with the array holding your numbers. and then sum. Thus you will get the same result as your formula.

SUMPRODUCT has the advantage that you do not need to Ctrl + Shift + Enter.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi deep_shobhit

This formula seems to be equivalent to any of your 3 formulas:

Code:
=SUM(N(INDIRECT(ADDRESS(ROW(),-1+MOD(COLUMN()+1,3)+3*ROW(INDIRECT("1:"&INT((COLUMN()-2)/3)))))))
Confirmed with CTRL+SHIFT+ENTER

If you try it, please post back the result.

Hope this helps
PGC
 

Forum statistics

Threads
1,141,679
Messages
5,707,787
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top