Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum variable range of column entries in offset cell
Hi chaps,
Many thanks those of you who tried to help with this question last week, but my simplified example wasn't good enough. I'll ask the question again but with a more detailed explanantion. Sorry for any induced headaches in advance. 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 in AV916:CO943). So from my first table (A915:AT943), my first formulas (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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum variable range of column entries in offset cell | New Users to Excel | |||
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 |