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