Calculating the average but formulas in blank cells are decreasing average!

**CLid** · 04-10-2013, 06:39 PM

Hi,

In my document I have two tabs that are linked. Sheet 2 extracts information from Sheet 1 and converts it to LN (cells G:BF). I am trying to calculate the average of the rows in Sheet 2 but because there are formulas in the cells (G:BF), it is counting it as a 0 therefore decreasing the average. I do not want to totally exclude 0s from the average formula because there is actual data that contains 0s that needs to be counted. Also, I add data each week to Sheet 1 and the formulas in Sheet 2 need to be able to automatically update. I have attached an example. I hope I explained it enough. Can someone please help? Many thanks

**Portuga** · 04-10-2013, 07:39 PM

On sheet 2 you are using the averagea formula.

Try using the average formula and see if it gives you what you are looking for.

On your formulas you should also remove "0"))) and change it to 0))) if you want the zeros to be considered as numeric.

**CLid** · 04-11-2013, 11:46 AM

I tried using the Average formula but then it doesn't calculate properly because I believe it is excluding the 0s that is why I used the AverageA function. I think the LN formula is transforming the data into maybe text? Take a look at rows 33-42, notice how the AverageA (column D) and Average (column F) are entirely different, they should be the same. Column D "AverageA (H3:AI3)" this is the average I believe that is correct, but the problem is that each time I add data I would have to recalculate the average to include the new column of data.

I removed the "" from the 0 in the formulas but then there is no data produced in the cell. Do you think I need to change my LN formula?

I really appreciate your help. I added a new example.

**Portuga** · 04-11-2013, 11:59 AM

The =average will disregard the "0"))) but will take into account 0)))

"0" is text
0 is numeric

As for your "real" zeros turning blanks you need to change you sheet options for zero display

Click the Microsoft Office Button Button image, click Excel Options, and then click the Advanced category.

Under Display options for this worksheet, select a worksheet, and then do one of the following:
To display zero (0) values in cells, select the Show a zero in cells that have zero value check box.
To display zero values as blank cells, clear the Show a zero in cells that have zero value check box.

**CLid** · 04-11-2013, 12:00 PM

So I realized that the data in Sheet 1 was formatted as "General" so I changed this to "Number". I then removed the "" from the 0 in the formula in Sheet 2 and it appears that this corrected the error in the average. You can see that column D, F and G now all have the same average. The problem now is that anytime there is a 0 in Sheet 1, the cell in Sheet 2 is blank when it should contain a 0 (rows 33-42). Any suggestions?

Your help is greatly appreciated!

**Jonmo1** · 04-11-2013, 12:07 PM

File - Options - Advanced - Display Options for this sheet
Check "Show a zero in cells that have a zero value"

**Portuga** · 04-11-2013, 12:08 PM

See my previous post.

**CLid** · 04-11-2013, 12:19 PM

That works perfectly!

Thank you so much for your help, I really really appreciate it

**CLid** · 04-25-2013, 05:30 PM

Hi,

I am still working on the same document and now I have run into another problem. I need the standard deviation and the average to be rolling so that each time new data is added in the last column the average and standard deviation will only be calculated on the last 26 cells in the row. Can you help please?

I tried using =AVERAGE(INDEX(3:3,MATCH(9.99999999999999E+307,3:3)-26):INDEX(3:3,MATCH(9.99999999999999E+307,3:3))) but when I compare it with =AVERAGE($H3:$AG3) which is the correct average for the last 26 cells the averages are not necessarily the same. The same goes for the standard deviation. Please see the attached document as an example.

Thank you!

**daddylonglegs** · 04-25-2013, 05:50 PM

By using -26 you are actually averaging the last 27 cells, try using -25, i.e.

=AVERAGE(INDEX(3:3,MATCH(9.99999999999999E+307,3:3)-25):INDEX(3:3,MATCH(9.99999999999999E+307,3:3)))

or you can use OFFSET like this

=AVERAGE(OFFSET(F3,0,COUNT(F3:BE3)-26,1,26))

(which does use -26!)

**CLid** · 04-25-2013, 06:12 PM

That's perfect!

Thanks very much!

Calculating the average but formulas in blank cells are decreasing average!

LinkBack

Thread Tools

Rate This Thread

Display

Calculating the average but formulas in blank cells are decreasing average!

Re: Calculating the average but formulas in blank cells are decreasing average!

Re: Calculating the average but formulas in blank cells are decreasing average!

Re: Calculating the average but formulas in blank cells are decreasing average!

Re: Calculating the average but formulas in blank cells are decreasing average!

Re: Calculating the average but formulas in blank cells are decreasing average!

Re: Calculating the average but formulas in blank cells are decreasing average!

Re: Calculating the average but formulas in blank cells are decreasing average!

Re: Calculating the average but formulas in blank cells are decreasing average!

Re: Calculating the average but formulas in blank cells are decreasing average!

Re: Calculating the average but formulas in blank cells are decreasing average!

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions