Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Partial / Conditional Sum of a Column
There are 12 figures in a column (A1:A12). B1 is a parameter Cell. when I
enter 3 in the parameter cell (B1), result must be {sum(A1:A3), sum(A4:A6), sum (A 7:A9), sum(a10:a11)}. I Mean to say sum function must be handled with the paramenter cell (B1), and result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Partial / Conditional Sum of a Column
Try this:
=IF(B$1="","",IF(ROWS($1:1)<=CEILING(COUNT(A$1:A$1 2)/B$1,1),SUM(OFFSET(A$1,(ROWS($1:1)-1)*B$1,,B$1)),"")) result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. If you use an interval of 1 then you have to copy the formula down to a number of cells that's equal to the size of your range. Biff "Mujeeb ur Rehman, FBL-GRW-PK" m wrote in message ... There are 12 figures in a column (A1:A12). B1 is a parameter Cell. when I enter 3 in the parameter cell (B1), result must be {sum(A1:A3), sum(A4:A6), sum (A 7:A9), sum(a10:a11)}. I Mean to say sum function must be handled with the paramenter cell (B1), and result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Partial / Conditional Sum of a Column
Caveat:
I'm assuming that you have all cells filled: There are 12 figures in a column Otherwise, the formula I suggested won't work as expected if there may be empty/blanks cells within the range. Biff "Biff" wrote in message ... Try this: =IF(B$1="","",IF(ROWS($1:1)<=CEILING(COUNT(A$1:A$1 2)/B$1,1),SUM(OFFSET(A$1,(ROWS($1:1)-1)*B$1,,B$1)),"")) result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. If you use an interval of 1 then you have to copy the formula down to a number of cells that's equal to the size of your range. Biff "Mujeeb ur Rehman, FBL-GRW-PK" m wrote in message ... There are 12 figures in a column (A1:A12). B1 is a parameter Cell. when I enter 3 in the parameter cell (B1), result must be {sum(A1:A3), sum(A4:A6), sum (A 7:A9), sum(a10:a11)}. I Mean to say sum function must be handled with the paramenter cell (B1), and result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Partial / Conditional Sum of a Column
Biff, thank u very much for your co-operation. I have applied your given
formula and got sufficient result but not 100%. Perhaps I could not explain my problem, well think about the following, which I want A B C Input para Result 1 1 3 - 2 2 - 3 3 6 4 4 - 5 5 - 6 6 15 7 7 - 8 8 - 9 9 24 10 10 - 11 11 - 12 12 33 Column A is a base column, B is parameter Column and C is Result Column. Now suppose I enter 6 in B1, the result must be (C1,C2,C3,C4,C5)=0 & C6=21 similarly (C7,C8,C9,C10,C11)=0 & C12 = 57. This is actually handling sum function with parameter. If you have any solution in this regard, please guide me. Hoping for best. Thanks again for contact. Mujeeb ur Rehman "Biff" wrote: Caveat: I'm assuming that you have all cells filled: There are 12 figures in a column Otherwise, the formula I suggested won't work as expected if there may be empty/blanks cells within the range. Biff "Biff" wrote in message ... Try this: =IF(B$1="","",IF(ROWS($1:1)<=CEILING(COUNT(A$1:A$1 2)/B$1,1),SUM(OFFSET(A$1,(ROWS($1:1)-1)*B$1,,B$1)),"")) result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. If you use an interval of 1 then you have to copy the formula down to a number of cells that's equal to the size of your range. Biff "Mujeeb ur Rehman, FBL-GRW-PK" m wrote in message ... There are 12 figures in a column (A1:A12). B1 is a parameter Cell. when I enter 3 in the parameter cell (B1), result must be {sum(A1:A3), sum(A4:A6), sum (A 7:A9), sum(a10:a11)}. I Mean to say sum function must be handled with the paramenter cell (B1), and result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Partial / Conditional Sum of a Column
If B1 is a parameter cell, B2:B12 is blank ?
Assume only B1 contain the parameter...B2:B12 is blank... Column A values may vary ? or a fixed increasing series numbers from 1 to n? Parameter B1 is a number of step for each cells with values, is it? Coz looking on sample may not guarantee the question.... "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Biff, thank u very much for your co-operation. I have applied your given formula and got sufficient result but not 100%. Perhaps I could not explain my problem, well think about the following, which I want A B C Input para Result 1 1 3 - 2 2 - 3 3 6 4 4 - 5 5 - 6 6 15 7 7 - 8 8 - 9 9 24 10 10 - 11 11 - 12 12 33 Column A is a base column, B is parameter Column and C is Result Column. Now suppose I enter 6 in B1, the result must be (C1,C2,C3,C4,C5)=0 & C6=21 similarly (C7,C8,C9,C10,C11)=0 & C12 = 57. This is actually handling sum function with parameter. If you have any solution in this regard, please guide me. Hoping for best. Thanks again for contact. Mujeeb ur Rehman "Biff" wrote: Caveat: I'm assuming that you have all cells filled: There are 12 figures in a column Otherwise, the formula I suggested won't work as expected if there may be empty/blanks cells within the range. Biff "Biff" wrote in message ... Try this: =IF(B$1="","",IF(ROWS($1:1)<=CEILING(COUNT(A$1:A$1 2)/B$1,1),SUM(OFFSET(A$1,(ROWS($1:1)-1)*B$1,,B$1)),"")) result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. If you use an interval of 1 then you have to copy the formula down to a number of cells that's equal to the size of your range. Biff "Mujeeb ur Rehman, FBL-GRW-PK" m wrote in message ... There are 12 figures in a column (A1:A12). B1 is a parameter Cell. when I enter 3 in the parameter cell (B1), result must be {sum(A1:A3), sum(A4:A6), sum (A 7:A9), sum(a10:a11)}. I Mean to say sum function must be handled with the paramenter cell (B1), and result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Partial / Conditional Sum of a Column
Maybe this one...
on cell C1 : =IF($B$1=1,A1,0) on cells C2:C12 : = IF($B$1=1,A12,IF(ROW(A12)/$B$1=ROUND(ROW(A12)/$B$1,0),SUM($A$1:A12)-SUM($C$1:C11),0)) hope this help... "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Biff, thank u very much for your co-operation. I have applied your given formula and got sufficient result but not 100%. Perhaps I could not explain my problem, well think about the following, which I want A B C Input para Result 1 1 3 - 2 2 - 3 3 6 4 4 - 5 5 - 6 6 15 7 7 - 8 8 - 9 9 24 10 10 - 11 11 - 12 12 33 Column A is a base column, B is parameter Column and C is Result Column. Now suppose I enter 6 in B1, the result must be (C1,C2,C3,C4,C5)=0 & C6=21 similarly (C7,C8,C9,C10,C11)=0 & C12 = 57. This is actually handling sum function with parameter. If you have any solution in this regard, please guide me. Hoping for best. Thanks again for contact. Mujeeb ur Rehman "Biff" wrote: Caveat: I'm assuming that you have all cells filled: There are 12 figures in a column Otherwise, the formula I suggested won't work as expected if there may be empty/blanks cells within the range. Biff "Biff" wrote in message ... Try this: =IF(B$1="","",IF(ROWS($1:1)<=CEILING(COUNT(A$1:A$1 2)/B$1,1),SUM(OFFSET(A$1,(ROWS($1:1)-1)*B$1,,B$1)),"")) result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. If you use an interval of 1 then you have to copy the formula down to a number of cells that's equal to the size of your range. Biff "Mujeeb ur Rehman, FBL-GRW-PK" m wrote in message ... There are 12 figures in a column (A1:A12). B1 is a parameter Cell. when I enter 3 in the parameter cell (B1), result must be {sum(A1:A3), sum(A4:A6), sum (A 7:A9), sum(a10:a11)}. I Mean to say sum function must be handled with the paramenter cell (B1), and result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Partial / Conditional Sum of a Column
Try this:
Assuming your data start from A1 In B1 =IF(MOD(A1,3)=0,SUM(INDIRECT("A"&1*ROW()-2):OFFSET(A1,0,0)),"") "Mujeeb ur Rehman, FBL-GRW-PK" wrote: There are 12 figures in a column (A1:A12). B1 is a parameter Cell. when I enter 3 in the parameter cell (B1), result must be {sum(A1:A3), sum(A4:A6), sum (A 7:A9), sum(a10:a11)}. I Mean to say sum function must be handled with the paramenter cell (B1), and result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Partial / Conditional Sum of a Column
I guess this does what the OP wants but as posted it has a circular
reference and the references are incorrect: C1 : =IF($B$1=1,A1,0) Change to: =IF($B$1=1,A1,"") on cells C2:C12 : =IF($B$1=1,A12,IF(ROW(A12)/$B$1=ROUND(ROW(A12)/$B$1,0),SUM($A$1:A12)-SUM($C$1:C11),0)) Change to: =IF($B$1=1,A2,IF(ROW(A2)/B$1=ROUND(ROW(A2)/B$1,0),SUM(A$1:A2)-SUM(C$1:C1),"")) Biff "4pinoy" wrote in message ... Maybe this one... on cell C1 : =IF($B$1=1,A1,0) on cells C2:C12 : = IF($B$1=1,A12,IF(ROW(A12)/$B$1=ROUND(ROW(A12)/$B$1,0),SUM($A$1:A12)-SUM($C$1:C11),0)) hope this help... "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Biff, thank u very much for your co-operation. I have applied your given formula and got sufficient result but not 100%. Perhaps I could not explain my problem, well think about the following, which I want A B C Input para Result 1 1 3 - 2 2 - 3 3 6 4 4 - 5 5 - 6 6 15 7 7 - 8 8 - 9 9 24 10 10 - 11 11 - 12 12 33 Column A is a base column, B is parameter Column and C is Result Column. Now suppose I enter 6 in B1, the result must be (C1,C2,C3,C4,C5)=0 & C6=21 similarly (C7,C8,C9,C10,C11)=0 & C12 = 57. This is actually handling sum function with parameter. If you have any solution in this regard, please guide me. Hoping for best. Thanks again for contact. Mujeeb ur Rehman "Biff" wrote: Caveat: I'm assuming that you have all cells filled: There are 12 figures in a column Otherwise, the formula I suggested won't work as expected if there may be empty/blanks cells within the range. Biff "Biff" wrote in message ... Try this: =IF(B$1="","",IF(ROWS($1:1)<=CEILING(COUNT(A$1:A$1 2)/B$1,1),SUM(OFFSET(A$1,(ROWS($1:1)-1)*B$1,,B$1)),"")) result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. If you use an interval of 1 then you have to copy the formula down to a number of cells that's equal to the size of your range. Biff "Mujeeb ur Rehman, FBL-GRW-PK" m wrote in message ... There are 12 figures in a column (A1:A12). B1 is a parameter Cell. when I enter 3 in the parameter cell (B1), result must be {sum(A1:A3), sum(A4:A6), sum (A 7:A9), sum(a10:a11)}. I Mean to say sum function must be handled with the paramenter cell (B1), and result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Partial / Conditional Sum of a Column
Hi biff....looks quick changing my formula 0 (zero) to "" (blank), but Majeeb
has a "-"....what do tou think, can a blank or a space be shown in excel ? just amazed...about the changing result... "Biff" wrote: I guess this does what the OP wants but as posted it has a circular reference and the references are incorrect: C1 : =IF($B$1=1,A1,0) Change to: =IF($B$1=1,A1,"") on cells C2:C12 : =IF($B$1=1,A12,IF(ROW(A12)/$B$1=ROUND(ROW(A12)/$B$1,0),SUM($A$1:A12)-SUM($C$1:C11),0)) Change to: =IF($B$1=1,A2,IF(ROW(A2)/B$1=ROUND(ROW(A2)/B$1,0),SUM(A$1:A2)-SUM(C$1:C1),"")) Biff "4pinoy" wrote in message ... Maybe this one... on cell C1 : =IF($B$1=1,A1,0) on cells C2:C12 : = IF($B$1=1,A12,IF(ROW(A12)/$B$1=ROUND(ROW(A12)/$B$1,0),SUM($A$1:A12)-SUM($C$1:C11),0)) hope this help... "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Biff, thank u very much for your co-operation. I have applied your given formula and got sufficient result but not 100%. Perhaps I could not explain my problem, well think about the following, which I want A B C Input para Result 1 1 3 - 2 2 - 3 3 6 4 4 - 5 5 - 6 6 15 7 7 - 8 8 - 9 9 24 10 10 - 11 11 - 12 12 33 Column A is a base column, B is parameter Column and C is Result Column. Now suppose I enter 6 in B1, the result must be (C1,C2,C3,C4,C5)=0 & C6=21 similarly (C7,C8,C9,C10,C11)=0 & C12 = 57. This is actually handling sum function with parameter. If you have any solution in this regard, please guide me. Hoping for best. Thanks again for contact. Mujeeb ur Rehman "Biff" wrote: Caveat: I'm assuming that you have all cells filled: There are 12 figures in a column Otherwise, the formula I suggested won't work as expected if there may be empty/blanks cells within the range. Biff "Biff" wrote in message ... Try this: =IF(B$1="","",IF(ROWS($1:1)<=CEILING(COUNT(A$1:A$1 2)/B$1,1),SUM(OFFSET(A$1,(ROWS($1:1)-1)*B$1,,B$1)),"")) result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. If you use an interval of 1 then you have to copy the formula down to a number of cells that's equal to the size of your range. Biff "Mujeeb ur Rehman, FBL-GRW-PK" m wrote in message ... There are 12 figures in a column (A1:A12). B1 is a parameter Cell. when I enter 3 in the parameter cell (B1), result must be {sum(A1:A3), sum(A4:A6), sum (A 7:A9), sum(a10:a11)}. I Mean to say sum function must be handled with the paramenter cell (B1), and result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Partial / Conditional Sum of a Column
can a blank or a space be shown in excel ?
Not sure what you mean? If the OP wants a dash: "-", just replace the "" with "-". Biff "4pinoy" wrote in message ... Hi biff....looks quick changing my formula 0 (zero) to "" (blank), but Majeeb has a "-"....what do tou think, can a blank or a space be shown in excel ? just amazed...about the changing result... "Biff" wrote: I guess this does what the OP wants but as posted it has a circular reference and the references are incorrect: C1 : =IF($B$1=1,A1,0) Change to: =IF($B$1=1,A1,"") on cells C2:C12 : =IF($B$1=1,A12,IF(ROW(A12)/$B$1=ROUND(ROW(A12)/$B$1,0),SUM($A$1:A12)-SUM($C$1:C11),0)) Change to: =IF($B$1=1,A2,IF(ROW(A2)/B$1=ROUND(ROW(A2)/B$1,0),SUM(A$1:A2)-SUM(C$1:C1),"")) Biff "4pinoy" wrote in message ... Maybe this one... on cell C1 : =IF($B$1=1,A1,0) on cells C2:C12 : = IF($B$1=1,A12,IF(ROW(A12)/$B$1=ROUND(ROW(A12)/$B$1,0),SUM($A$1:A12)-SUM($C$1:C11),0)) hope this help... "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Biff, thank u very much for your co-operation. I have applied your given formula and got sufficient result but not 100%. Perhaps I could not explain my problem, well think about the following, which I want A B C Input para Result 1 1 3 - 2 2 - 3 3 6 4 4 - 5 5 - 6 6 15 7 7 - 8 8 - 9 9 24 10 10 - 11 11 - 12 12 33 Column A is a base column, B is parameter Column and C is Result Column. Now suppose I enter 6 in B1, the result must be (C1,C2,C3,C4,C5)=0 & C6=21 similarly (C7,C8,C9,C10,C11)=0 & C12 = 57. This is actually handling sum function with parameter. If you have any solution in this regard, please guide me. Hoping for best. Thanks again for contact. Mujeeb ur Rehman "Biff" wrote: Caveat: I'm assuming that you have all cells filled: There are 12 figures in a column Otherwise, the formula I suggested won't work as expected if there may be empty/blanks cells within the range. Biff "Biff" wrote in message ... Try this: =IF(B$1="","",IF(ROWS($1:1)<=CEILING(COUNT(A$1:A$1 2)/B$1,1),SUM(OFFSET(A$1,(ROWS($1:1)-1)*B$1,,B$1)),"")) result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. If you use an interval of 1 then you have to copy the formula down to a number of cells that's equal to the size of your range. Biff "Mujeeb ur Rehman, FBL-GRW-PK" m wrote in message ... There are 12 figures in a column (A1:A12). B1 is a parameter Cell. when I enter 3 in the parameter cell (B1), result must be {sum(A1:A3), sum(A4:A6), sum (A 7:A9), sum(a10:a11)}. I Mean to say sum function must be handled with the paramenter cell (B1), and result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Partial / Conditional Sum of a Column
nice catch for a long formula...
"Biff" wrote: can a blank or a space be shown in excel ? Not sure what you mean? If the OP wants a dash: "-", just replace the "" with "-". Biff "4pinoy" wrote in message ... Hi biff....looks quick changing my formula 0 (zero) to "" (blank), but Majeeb has a "-"....what do tou think, can a blank or a space be shown in excel ? just amazed...about the changing result... "Biff" wrote: I guess this does what the OP wants but as posted it has a circular reference and the references are incorrect: C1 : =IF($B$1=1,A1,0) Change to: =IF($B$1=1,A1,"") on cells C2:C12 : =IF($B$1=1,A12,IF(ROW(A12)/$B$1=ROUND(ROW(A12)/$B$1,0),SUM($A$1:A12)-SUM($C$1:C11),0)) Change to: =IF($B$1=1,A2,IF(ROW(A2)/B$1=ROUND(ROW(A2)/B$1,0),SUM(A$1:A2)-SUM(C$1:C1),"")) Biff "4pinoy" wrote in message ... Maybe this one... on cell C1 : =IF($B$1=1,A1,0) on cells C2:C12 : = IF($B$1=1,A12,IF(ROW(A12)/$B$1=ROUND(ROW(A12)/$B$1,0),SUM($A$1:A12)-SUM($C$1:C11),0)) hope this help... "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Biff, thank u very much for your co-operation. I have applied your given formula and got sufficient result but not 100%. Perhaps I could not explain my problem, well think about the following, which I want A B C Input para Result 1 1 3 - 2 2 - 3 3 6 4 4 - 5 5 - 6 6 15 7 7 - 8 8 - 9 9 24 10 10 - 11 11 - 12 12 33 Column A is a base column, B is parameter Column and C is Result Column. Now suppose I enter 6 in B1, the result must be (C1,C2,C3,C4,C5)=0 & C6=21 similarly (C7,C8,C9,C10,C11)=0 & C12 = 57. This is actually handling sum function with parameter. If you have any solution in this regard, please guide me. Hoping for best. Thanks again for contact. Mujeeb ur Rehman "Biff" wrote: Caveat: I'm assuming that you have all cells filled: There are 12 figures in a column Otherwise, the formula I suggested won't work as expected if there may be empty/blanks cells within the range. Biff "Biff" wrote in message ... Try this: =IF(B$1="","",IF(ROWS($1:1)<=CEILING(COUNT(A$1:A$1 2)/B$1,1),SUM(OFFSET(A$1,(ROWS($1:1)-1)*B$1,,B$1)),"")) result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. If you use an interval of 1 then you have to copy the formula down to a number of cells that's equal to the size of your range. Biff "Mujeeb ur Rehman, FBL-GRW-PK" m wrote in message ... There are 12 figures in a column (A1:A12). B1 is a parameter Cell. when I enter 3 in the parameter cell (B1), result must be {sum(A1:A3), sum(A4:A6), sum (A 7:A9), sum(a10:a11)}. I Mean to say sum function must be handled with the paramenter cell (B1), and result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Partial / Conditional Sum of a Column
from your post reading it as "the result must be (C1,C2,C3,C4,C5)=0 & C6=21"
Meaning no blank or minus character ......."" or "-" as sub-results.... Hope this fits clearly your request and without confusing ideas... from C2 =IF($B$1=1,A2,IF(ROW(A2)/$B$1=ROUND(ROW(A2)/$B$1,0),SUM($A$1:A2)-SUM($C$1:C1),0)) copy paste down to C12 in C1 =IF($B$1=1,A1,0) .... "Biff" wrote: can a blank or a space be shown in excel ? Not sure what you mean? If the OP wants a dash: "-", just replace the "" with "-". Biff "4pinoy" wrote in message ... Hi biff....looks quick changing my formula 0 (zero) to "" (blank), but Majeeb has a "-"....what do tou think, can a blank or a space be shown in excel ? just amazed...about the changing result... "Biff" wrote: I guess this does what the OP wants but as posted it has a circular reference and the references are incorrect: C1 : =IF($B$1=1,A1,0) Change to: =IF($B$1=1,A1,"") on cells C2:C12 : =IF($B$1=1,A12,IF(ROW(A12)/$B$1=ROUND(ROW(A12)/$B$1,0),SUM($A$1:A12)-SUM($C$1:C11),0)) Change to: =IF($B$1=1,A2,IF(ROW(A2)/B$1=ROUND(ROW(A2)/B$1,0),SUM(A$1:A2)-SUM(C$1:C1),"")) Biff "4pinoy" wrote in message ... Maybe this one... on cell C1 : =IF($B$1=1,A1,0) on cells C2:C12 : = IF($B$1=1,A12,IF(ROW(A12)/$B$1=ROUND(ROW(A12)/$B$1,0),SUM($A$1:A12)-SUM($C$1:C11),0)) hope this help... "Mujeeb ur Rehman, FBL-GRW-PK" wrote: Biff, thank u very much for your co-operation. I have applied your given formula and got sufficient result but not 100%. Perhaps I could not explain my problem, well think about the following, which I want A B C Input para Result 1 1 3 - 2 2 - 3 3 6 4 4 - 5 5 - 6 6 15 7 7 - 8 8 - 9 9 24 10 10 - 11 11 - 12 12 33 Column A is a base column, B is parameter Column and C is Result Column. Now suppose I enter 6 in B1, the result must be (C1,C2,C3,C4,C5)=0 & C6=21 similarly (C7,C8,C9,C10,C11)=0 & C12 = 57. This is actually handling sum function with parameter. If you have any solution in this regard, please guide me. Hoping for best. Thanks again for contact. Mujeeb ur Rehman "Biff" wrote: Caveat: I'm assuming that you have all cells filled: There are 12 figures in a column Otherwise, the formula I suggested won't work as expected if there may be empty/blanks cells within the range. Biff "Biff" wrote in message ... Try this: =IF(B$1="","",IF(ROWS($1:1)<=CEILING(COUNT(A$1:A$1 2)/B$1,1),SUM(OFFSET(A$1,(ROWS($1:1)-1)*B$1,,B$1)),"")) result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. If you use an interval of 1 then you have to copy the formula down to a number of cells that's equal to the size of your range. Biff "Mujeeb ur Rehman, FBL-GRW-PK" m wrote in message ... There are 12 figures in a column (A1:A12). B1 is a parameter Cell. when I enter 3 in the parameter cell (B1), result must be {sum(A1:A3), sum(A4:A6), sum (A 7:A9), sum(a10:a11)}. I Mean to say sum function must be handled with the paramenter cell (B1), and result will be in other column with parameter interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup function/sum function | Excel Discussion (Misc queries) | |||
How do I combine spreadsheets and documents in one file? | Excel Discussion (Misc queries) | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
conditional formatting based on column | Excel Discussion (Misc queries) |