Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Tricky one
Hi all,
I have the following example set of data n|A|B 1|1|10 2|2|55 3|2|34 4|1|12 5|3|45 6|2|344 7|3|32 8|3|56 9|2|23 Each row needs to have a sum formula in row C, which needs to sum the "last n rows" (say n is 3 for this example) where the value in A is NOT 3 So: In row 3, it would sum and return rows 1 2 3. In row 4, it would sum and return rows 2 3 4 In row 6, it would sum and return rows 6 4 3 (skipping row 5) In row 9, it would sum and return rows 9 6 5 (skipping rows 8 and 7) I cant seem to work this out, even using fancy sumproduct/match combinations. Don't want to use VBA Many thanks! James |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky one
"LondonJames" ha scritto nel messaggio ... Hi all, I have the following example set of data n|A|B 1|1|10 2|2|55 3|2|34 4|1|12 5|3|45 6|2|344 7|3|32 8|3|56 9|2|23 Each row needs to have a sum formula in row C, which needs to sum the "last n rows" (say n is 3 for this example) where the value in A is NOT 3 So: In row 3, it would sum and return rows 1 2 3. In row 4, it would sum and return rows 2 3 4 In row 6, it would sum and return rows 6 4 3 (skipping row 5) In row 9, it would sum and return rows 9 6 5 (skipping rows 8 and 7) I cant seem to work this out, even using fancy sumproduct/match combinations. Don't want to use VBA Many thanks! James -- LondonJames Put in cell I1 the value escluded (3) In C4 this matricial formula: =SUM((OFFSET(A4,,,-MATCH($I$1,COUNTIF(OFFSET(A4,,,-ROW(INDIRECT("$A$1:A"&ROW())),),"<"&$I$1)),)<$I$ 1)*OFFSET(A4,,1,-MATCH($I$1,COUNTIF(OFFSET(A4,,,-ROW(INDIRECT("$A$1:A"&ROW())),),"<"&$I$1)),)) and fill down. Bye, E. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
This One Is Tricky Need Help !!!!! | Excel Discussion (Misc queries) | |||
A Tricky One...... | Excel Worksheet Functions | |||
This might be a little tricky...... | Excel Discussion (Misc queries) | |||
Is it just me or is this tricky? | Excel Discussion (Misc queries) | |||
Tricky maybe | Excel Worksheet Functions |