Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating a sum from a given number of rows
Hello,
I'm looking for a formula that can calculate the sum of a number of rows given. In the example below the formula should output the sum of the figures from the last 7 rows (starting from the right to the left). --------------------------------------------- Rows (from right to left) A1: 7 A B C D E F G H I J K 4 5 3 2 8 9 5 6 3 2 4 Output: sum rows E-K --------------------------------------------- I used to calculate this using a IF formula, however that only worked until i reached the maximum of 6 IF brackets "()". Does anybody has an idea? Thanks!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating a sum from a given number of rows
One way:
=SUM(OFFSET(E1,0,0,1,A1) Note: The standard term for the group of cells going down under "A" is a column. The standard interpretation of 'row' is cells going across. In article , tunupo wrote: Hello, I'm looking for a formula that can calculate the sum of a number of rows given. In the example below the formula should output the sum of the figures from the last 7 rows (starting from the right to the left). --------------------------------------------- Rows (from right to left) A1: 7 A B C D E F G H I J K 4 5 3 2 8 9 5 6 3 2 4 Output: sum rows E-K --------------------------------------------- I used to calculate this using a IF formula, however that only worked until i reached the maximum of 6 IF brackets "()". Does anybody has an idea? Thanks!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating a sum from a given number of rows
It depends!
You want the sum of the last 7 entries from right to left. What if there aren't 7 entries? Might there be any empty/blank cells in-between? Like this: A B C D E F G H I J K 4 5 3 2 8 9 6 3 4 In that case, do you want the sum of the last 7 entries or the last 7 cells. If you want the last 7 *entries* the result would be 35. If you want the sum of the last 7 *cells* the result would be 30. -- Biff Microsoft Excel MVP "tunupo" wrote in message ... Hello, I'm looking for a formula that can calculate the sum of a number of rows given. In the example below the formula should output the sum of the figures from the last 7 rows (starting from the right to the left). --------------------------------------------- Rows (from right to left) A1: 7 A B C D E F G H I J K 4 5 3 2 8 9 5 6 3 2 4 Output: sum rows E-K --------------------------------------------- I used to calculate this using a IF formula, however that only worked until i reached the maximum of 6 IF brackets "()". Does anybody has an idea? Thanks!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating a sum from a given number of rows
Thanks for your helpful replies. I could work it out as follows:
=Sum(Offset(K1,0,0,1,-A1)) Whereas K1 stands for the last column with numbers and A1 stands for the input of number of cells to calculate back. Many thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating rows | New Users to Excel | |||
Calculating volume and number. | Excel Worksheet Functions | |||
Inserted Rows not re-calculating | Excel Discussion (Misc queries) | |||
Text number to number value for calculating | Excel Worksheet Functions | |||
deleting rows--calculating formulas with each row as it goes? | Excel Discussion (Misc queries) |