Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic display
Hi,
I wonder if it is possible to do this without using a macro. Columns A to F have values and everyday I am adding new values building a list. What I would like to do is on column G show the my formula result one row below the last row that has data on thos columns. So say row 10 is the last row with data, so I want to have a row 11 column G the result of the formula. The next day when I add data to row 11 then, then column G row 11 will be empty and row 12 will have the formula result and so on. To complicate things I have 10 different formula calculations that I would like displayed. So what I would like this to accomplish is to display a range from row 11 to row 21 (using my example) on column G. In the end this is like a dynamic table where the result shows at the first empty row. Can this be done without using a macro? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic display
On Thu, 16 Jul 2009 07:14:35 -0700 (PDT), "
wrote: Hi, I wonder if it is possible to do this without using a macro. Columns A to F have values and everyday I am adding new values building a list. What I would like to do is on column G show the my formula result one row below the last row that has data on thos columns. So say row 10 is the last row with data, so I want to have a row 11 column G the result of the formula. The next day when I add data to row 11 then, then column G row 11 will be empty and row 12 will have the formula result and so on. To complicate things I have 10 different formula calculations that I would like displayed. So what I would like this to accomplish is to display a range from row 11 to row 21 (using my example) on column G. In the end this is like a dynamic table where the result shows at the first empty row. Can this be done without using a macro? It can be done, but I don't know if it is a good idea. Assuming that the number of data in columns A to F are the same for all six columns, you may try the following formula in cell G1: =CHOOSE(MIN(13,MAX(1,ROW()+2-MATCH(TRUE,ISBLANK(F$1:F$100),0))),"",1,2,3,4,5,6, 7,8,9,10,11,"") Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Replace 1,2,3,....,11 with your eleven formulas. Maybe something like this (where I only put in examples for the first three). =CHOOSE(MIN(13,MAX(1,ROW()+2-MATCH(TRUE,ISBLANK(F$1:F$100),0))),"",SUM(A$1:F$10 0),AVERAGE(A$1:F$100),STDEV(A$1:F$100),4,5,6,7,8,9 ,10,11,"") Change F$100 to fit the maximum size of your data i columns A to F and copy the formula down as far as needed in column G. If the number of formulas you have is not eleven, just change the 13 to the actual number of formula plus 2 and adapt the list of formulas. Hope this helps / Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display Dynamic Range | Excel Worksheet Functions | |||
Dynamic Chart Display | Charts and Charting in Excel | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Dynamic Time Display | Excel Worksheet Functions |