Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum variable range of column entries in offset cell
Hi chaps,
Im trying to look up a column of values from an offset cell and sum the column values (or depending on formula used, work out the average, median or mode of the data range in the column). The hard bit is I need to specify (using a variable number value in another cell) how many cells up from the bottom of the column are included in my calculations. Heres a simplified example to (hopefully) try and explain more clearly: A B C D E F G 1 5 2 2 5 3 0 4 3 5 1 6 7 7 1 8 4 9 Formula B1:B8 contain the values I want to use in my calculations. G2 contains the (variable) value that tells me how many cells from B8 upwards I want in my calculation. F9 contains the formula. In the example, Formula (F9) needs to SUM B4:B8 (last 5 entries in the column). Depending on my needs the formula could also either be Average, Median or Mode of the number of cells specified. Sum = 16 Average = 3.2 Median = 3 Mode = 1 Hope my explanation is clear enough and you can help. Many thanks for looking, Steve. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum variable range of column entries in offset cell
Ctrl/Shift/Enter:
=SUM(TRANSPOSE(OFFSET(B1,COUNT(B:B)-G2,0,G2,1))) Bob Umlas Excel MVP "Struggling in Sheffield" wrote in message ... Hi chaps, I'm trying to look up a column of values from an offset cell and sum the column values (or depending on formula used, work out the average, median or mode of the data range in the column). The hard bit is I need to specify (using a variable number value in another cell) how many cells up from the bottom of the column are included in my calculations. Here's a simplified example to (hopefully) try and explain more clearly: A B C D E F G 1 5 2 2 5 3 0 4 3 5 1 6 7 7 1 8 4 9 Formula B1:B8 contain the values I want to use in my calculations. G2 contains the (variable) value that tells me how many cells from B8 upwards I want in my calculation. F9 contains the formula. In the example, Formula (F9) needs to SUM B4:B8 (last 5 entries in the column). Depending on my needs the formula could also either be Average, Median or Mode of the number of cells specified. Sum = 16 Average = 3.2 Median = 3 Mode = 1 Hope my explanation is clear enough and you can help. Many thanks for looking, Steve. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum variable range of column entries in offset cell
=SUM(OFFSET(B1,COUNT(B:B),,-G2-1,))
"Struggling in Sheffield" wrote: Hi chaps, Im trying to look up a column of values from an offset cell and sum the column values (or depending on formula used, work out the average, median or mode of the data range in the column). The hard bit is I need to specify (using a variable number value in another cell) how many cells up from the bottom of the column are included in my calculations. Heres a simplified example to (hopefully) try and explain more clearly: A B C D E F G 1 5 2 2 5 3 0 4 3 5 1 6 7 7 1 8 4 9 Formula B1:B8 contain the values I want to use in my calculations. G2 contains the (variable) value that tells me how many cells from B8 upwards I want in my calculation. F9 contains the formula. In the example, Formula (F9) needs to SUM B4:B8 (last 5 entries in the column). Depending on my needs the formula could also either be Average, Median or Mode of the number of cells specified. Sum = 16 Average = 3.2 Median = 3 Mode = 1 Hope my explanation is clear enough and you can help. Many thanks for looking, Steve. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum variable range of column entries in offset cell
Assuming there are no empty cells *within* the range and there is no other
unrelated numeric data further down the column. =FUNCTION(INDEX(B:B,COUNT(B:B)):INDEX(B:B,ABS(G2-1-COUNT(B:B)))) Replace FUNCTION with the appropriate function: SUM, AVERAGE, MODE, etc. -- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi chaps, I'm trying to look up a column of values from an offset cell and sum the column values (or depending on formula used, work out the average, median or mode of the data range in the column). The hard bit is I need to specify (using a variable number value in another cell) how many cells up from the bottom of the column are included in my calculations. Here's a simplified example to (hopefully) try and explain more clearly: A B C D E F G 1 5 2 2 5 3 0 4 3 5 1 6 7 7 1 8 4 9 Formula B1:B8 contain the values I want to use in my calculations. G2 contains the (variable) value that tells me how many cells from B8 upwards I want in my calculation. F9 contains the formula. In the example, Formula (F9) needs to SUM B4:B8 (last 5 entries in the column). Depending on my needs the formula could also either be Average, Median or Mode of the number of cells specified. Sum = 16 Average = 3.2 Median = 3 Mode = 1 Hope my explanation is clear enough and you can help. Many thanks for looking, Steve. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum variable range of column entries in offset cell
On Fri, 2 Oct 2009 10:35:01 -0700, Struggling in Sheffield
wrote: Hi chaps, I’m trying to look up a column of values from an offset cell and sum the column values (or depending on formula used, work out the average, median or mode of the data range in the column). The hard bit is I need to specify (using a variable number value in another cell) how many cells up from the bottom of the column are included in my calculations. Here’s a simplified example to (hopefully) try and explain more clearly: A B C D E F G 1 5 2 2 5 3 0 4 3 5 1 6 7 7 1 8 4 9 Formula B1:B8 contain the values I want to use in my calculations. G2 contains the (variable) value that tells me how many cells from B8 upwards I want in my calculation. F9 contains the formula. In the example, Formula (F9) needs to SUM B4:B8 (last 5 entries in the column). Depending on my needs the formula could also either be Average, Median or Mode of the number of cells specified. Sum = 16 Average = 3.2 Median = 3 Mode = 1 Hope my explanation is clear enough and you can help. Many thanks for looking, Steve. With no blank cells: =SUM(OFFSET($B$1,COUNT(B:B)-1,0,-$G$2,1)) --ron |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum variable range of column entries in offset cell
Hi again chaps,
Sorry for the delayed response, have been away for a few days. The situation is not as straightforward as my original post. Will attempt a more comprehensive explanation which may invoke a headache: - I have 900 lines of data (A3:AT902). Below this I am using an INDIRECT formula to pull down (copy) certain lines from this mass of data (depending on certain entries within the data) into 60 separate tables below. First table (A915:AT943), second table (A951:AT979), etc. Much of the lines of data copied down into my 60 tables is numeric, and on these I then need to perform various sum, average, median and mode calculations (calcs on first table performed in AV916:CO943). So from my first table (A915:AT943) my first calculations (AV916:AV943) need to find the sum of cells in column G one row above the formula cell and for a specified number of cells (e.g. 6 cells) above this. The number of cells that need including in the calculation is referenced from another worksheet (Admin sheet!$O$22). In the example below (using my real table cell references), value in 'Admin sheet!'$O$22 is 6. Formula in AV943 needs to sum the values in column G starting one row above Row 943 (in G942) for 6 cells (G937:G942) (answer 18). Formula in AV942 needs to sum the values in column G starting one row above Row 942 (in G941) for 6 cells (G936:G941) (answer 19). Etc. If any cell in column G that is part of the calculation is blank, then the formula in column AV needs to return a blank also. A - G - AT - AV - CO 915 5 916 2 Formula 917 0 Formula 918 3 Formula - - 936 2 Formula 937 3 Formula 938 5 Formula 939 1 Formula 940 1 Formula 941 7 Formula 942 1 Formula 943 4 Formula Many thanks again for looking. "Struggling in Sheffield" wrote: Hi chaps, Im trying to look up a column of values from an offset cell and sum the column values (or depending on formula used, work out the average, median or mode of the data range in the column). The hard bit is I need to specify (using a variable number value in another cell) how many cells up from the bottom of the column are included in my calculations. Heres a simplified example to (hopefully) try and explain more clearly: A B C D E F G 1 5 2 2 5 3 0 4 3 5 1 6 7 7 1 8 4 9 Formula B1:B8 contain the values I want to use in my calculations. G2 contains the (variable) value that tells me how many cells from B8 upwards I want in my calculation. F9 contains the formula. In the example, Formula (F9) needs to SUM B4:B8 (last 5 entries in the column). Depending on my needs the formula could also either be Average, Median or Mode of the number of cells specified. Sum = 16 Average = 3.2 Median = 3 Mode = 1 Hope my explanation is clear enough and you can help. Many thanks for looking, Steve. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable range column summation and averaging | Excel Discussion (Misc queries) | |||
Concatenate column with variable to set a range | Excel Discussion (Misc queries) | |||
If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
Transpose? Offset? Variable starting cell | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |