This spreadsheet has rows with just numerical data in the cells. Many of the cells are blank. Sometimes the data in one cell is identical to the cell immediately above it. These are called repeaters. At the end of each row there is a cell where I manually input the number of repeaters in that row. The next cell after that cell calculates the average of the rows that have repeaters in them from the top of the column to the current cell. I am not calculating the number of repeaters, just the percentage of rows that have repeaters in them. I use this formula for that;
IF(BQ976>0,COUNT(BQ$976: BQ976)/(COUNT(BQ$976: BQ976)+COUNTBLANK(BQ$976: BQ976)),"")
When I drag this formula down the column, BQ976:BQ1009, it indexes automatically so it calculates the percentage for each row from the top of this column to the bottom as the data is input. The last cell in that column, BQ1010, calculates the average of the averages from BQ976 to BQ1009. It is this cell, BQ1010, which is giving me problems.
The data rows are 61 columns wide, G976:BO976. I want to automatically mirror a parallel row from BR976 to DZ976, but instead of entering the data from G976:BO976, I want to enter the average of the averages from BQ1010 in each mirrored cell. So if BQ1010 has a displayed value of 0.71 and G976 has numerical data in it, then I want BR976 to have the value 0.71 not the formula from BQ1010. I have been using this formula, IF(G976>0,$BR$1010,"") by placing it in BR976 and dragging it across to DZ976. The G976 indexes so any cell in G976:BO976 that has data in it gets 0.71 in its mirror in BR976:DZ976. That is what I want. The problem is that when I enter data in the next row G977:BO977, BQ1010 changes and the 0.71 changes in the first row because my formula enters the formula from BQ1010. I need the 0.71 to remain fixed in the first row. How do I do that?