Can an “If statement” be setup to return a numerical value from a cell but not the formula in that cell that calculated the value?

AlbusD · Apr 4, 2013

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.
[FONT=&quot]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?[/FONT]

Hamburgler · Apr 5, 2013

Not sure if I am following, but is the INDIRECT() function of any use? Also OFFSET(), ROW() and COLUMN() functions are sometimes useful with INDIRECT().

AlbusD · Apr 5, 2013

I will look at each of those function. If you can tell me how to post a spreadsheet page, I will post mine. That will make things clearer. Thank you very much for your effort and patience.

AlbusD · Apr 6, 2013

INDIRECT() Did not work for my problem. If you can tell me how to post a spreadsheet page I can show you what I am trying to do.

Hamburgler · Apr 8, 2013

I am not sure. It is not in the sticky threads at the top. Does anyone know? Is it just the insert table feature in the (advanced) message editor?

1	a
2	b
3	c

Michael M · Apr 8, 2013

You can't upload sheets directly to this forum
Have a look at my tag and download the "HTML Maker".
This will allow you to post a SMALL sample of your data.
Otherwise, use an upload site, like Dropbox or Mediafire and then post a link to the site back here.

Can an “If statement” be setup to return a numerical value from a cell but not the formula in that cell that calculated the value?

AlbusD

New Member

Excel Facts

Hamburgler

Board Regular

AlbusD

New Member

AlbusD

New Member

Hamburgler

Board Regular

Michael M

Well-known Member

Similar threads

Forum statistics

Share this page

Can an “If statement” be setup to return a numerical value from a cell but not the formula in that cell that calculated the value?

AlbusD

New Member

Excel Facts

Hamburgler

Board Regular

AlbusD

New Member

AlbusD

New Member

Hamburgler

Board Regular

Michael M

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock