If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Sum variable range of column entries in offset cell
Some thoughts for these lines from your posting ..
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). Maybe something simple like this using OFFSET would satisfy .. In AV942: =SUM(OFFSET(G941,,,-6)) This returns the sum of G936:G941, ie 6 cells counting from the OFFSET's anchor G941 "up" (its "up" because of the negative "-6" used as the height param). Copy AV942 down 1 cell to AV943, which increments the formula to: =SUM(OFFSET(G942,,,-6)) hence returning what you specify above (for AV943), ie the sum of G937:G942 Any good? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
#3
|
|||
|
|||
Sum variable range of column entries in offset cell
Hi Max,
Thanks for that, adapted it slightly for my circumstances and that works fine now. Cheers "Max" wrote: Some thoughts for these lines from your posting .. 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). Maybe something simple like this using OFFSET would satisfy .. In AV942: =SUM(OFFSET(G941,,,-6)) This returns the sum of G936:G941, ie 6 cells counting from the OFFSET's anchor G941 "up" (its "up" because of the negative "-6" used as the height param). Copy AV942 down 1 cell to AV943, which increments the formula to: =SUM(OFFSET(G942,,,-6)) hence returning what you specify above (for AV943), ie the sum of G937:G942 Any good? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
Thread Tools | |
Display Modes | |
|
|