Free Microsoft Excel 2013 Quick Reference

Excluding Blank Cells in an Average Formula

Good Morning,

I am trying to average columns of data, but want to exclude cells from the average if they are blank . Is there any way to do this? I am thinking maybe a countif statement in conjunction with an average function?

Thank you,


Post your answer or comment

comments powered by Disqus
I am trying to ignore blank cells in a certain column. Columns A and B contain data. Column A contains number between 2 and 11, and column B contains measured data.
The formula I am using is as follows:

{=VAR(AVERAGE(IF(A1:A100=2,B1:B100,"")),......}

This formula goes on using 2 to 11 in the logic test statement and takes the variance. But, if one of the cells is blank in the B column, an error occurs in the IF statement evaluation, leading to an error as the final answer.
This workbook will be used by others, and there is no way to predetermine which cells will be blank when data is pasted into the worksheet.

Thanks.

The following array formula works great when the cells in the specified ranges are not blank. But if I were to include blank cells in the ranges I come up with an #N/A error. I don't know how to include something in the function to add the blank cells as 0.

{=SUM(IF((Sev=1)*(Comp=1),1,0))}

Any help is greatly appreciated!! Thank you!!!!!

I'm keeping track of daily stats and having excel keep a running average as i put in the days statistics. The probelm is if I omitt a day the 0 from that cell will included in the calculations.

Example.

5 days worth of date (time formate 00:00:00)

First day 00:05:25
Second day 00:06:01
Third day 00:00:00 (omitted day that i dont' want to be in the calc)
fourth day 00:05:35
Firth day 00:05:47

I'm getting 4:34 because of the zero day instead of the 5:42 actual average.

Is there any way to get a average formula to omit zero's?

thanks

If an "average" formula includes multiple columns, and those columns change
each month by hiding old months (at end) and adding new months (at beginning)
what can be done, by macro or in formula) to ignore the hidden columns?

How do I exclude blank cells in diagrams. If I have an area of data and among
these data some is blank. How do I get excel to not display these data as '0'
but just to skip the cell.

Im looking for help in removing blank cells from an array and condensing the array so that all non-zero values are listed in touching cells.

ex:

(blank) value1 (blank) value2

to

value1 value2

thanks.

Hello

Using conditional formatting to color duplicate cells in one column. If the formula does not return anything the cell is coloured because of other blank cells in the column. Formula in J3 is =RIGHT(F3,4) data in F3 is alphanumeric formatted as Text.
I have tried Condition 1 as =IF($J$3:$J$200,J3)1
Can I stop the color appearing in blank cells.
Thanks

Hi All,

Hopefully a simple one for you:

I have a UD function, where the user selects 4 cells. I'm trying to get the function to simply match each of these against each other and place a True in a variable if any of them match with each other.

The way I have done this is probably not the best way:

If Source1.Text = Source2.Text Or Source1.Text = Source3.Text Or Source1.Text = Source4.Text Or Source2.Text = Source3.Text
Or Source2.Text = Source4.Text Or Source3.Text = Source4.Text Then

Results4 = True
End If
[Code/]

The problem I get - is that this looks at Blank cells and compares against other blank cells - which I dont want to happen.

Is there any way I can exclude blank cells in my code?

Thanks in advance.

I'm using the following formula to get an average of specific percentages matching specific cells in a specific column

=SUMPRODUCT(($B$2:$B$169="Extra")*((AA$2:AA$169)/COUNTIF($B$2:$B$169,"=Extra")))

i.e under each monthly column this formula displays the average for all 'Extra' services that month.

Column A = List of sites
Column B = Standard, Premium, Extra
Columns C to X = Percentages for that month

Site : Service : May 09 : June 09 : July 09
SITE A : Standard : 100% : 90.99% : 95.34%
SITE A : Premium : 100% : [Blank] : 95.34%
SITE A : Extra : 95.55% : 90.99% : 95.34%
SITE B : Standard : 100% : 90.99% : [Blank]
SITE B : Premium : 100% : 90.99% : 95.34%
SITE B : Extra : 95.55% : [Blank] : 93.34%
SITE C : Standard : 100% : 90.99% : 95.34%
SITE C : Premium : [Blank] : 90.99% : 95.24%
SITE C : Extra : 95.55% : 90.99% : 95.34%

The formula is working perfectly except when the percentage column has a blank cell, which returns an incorrect result.

Is there a way to adapt this formula so that blank cells are ignored data range of percentages.

Please note I don't want to change the order of the rows in the table.

Thanks in advance!

I was kindly provided by Mike Rickson with the formula to calculate the averages one level up in WBS structure.
=SUMIF($A$3:$A$44, A3&".?", $C$3:$C$44) / COUNTIF($A$3:$A$44, A3&".?")
It works fine, but treats blank cells as cells with zeros. I was racking my brain to have it ignore empty cells from calculations but to no avail.
My logic behind this is that if there is no value, it never happened and was not planned, so there is no point to take it into consideration.
How can I have the formula ignore blank cells?
Thanks a lot in advance.

if i use "" in an IF formula, then it generates a cell which looks empty.
however, when i then use the data series to generate a scatter graph, my
whole series disppears, including the cells which contain numeric data.

is there another way of creating a blank cell through use of a formula?

Thank you

Hello!

An example of my data set looks like this

colA B C D E F G
year (annual tree ring width....each column is an individual tree)
1780 0 0 0 1 0 0
1781 0 2 0 1 2 0
1782 3 4 0 5 3 4
1783 3 1 2 4 2 4
1784 3 3 3 3 3 3
1785 3 4 3 1 2 1
1786 5 2 1 3 2 1
etc..etc..etc

What I am trying to do is get the average of the first 5 non-blank data values in each column.

However, some columns have blank cells (0) before the actual data values start. How can I get an average of the first 5 non blank cells in each column?

(I want to know the average tree ring width over the first 5 years of growth for each tree)

Thanks for any help...have looked all over for this answer! I have at least 800 columns of data and def could use a short cut!

Hey all,

I'm trying to run down through a very long/variable Column Y and add a SUM formula of the cells in Columns J:X to all blank cells in which the row also has an account number in Column A. (I've attached a truncated example of the spreadsheet). Basically, if there is a blank spot in the totals column that also has an account in column A, I need to sum a bunch of the values in between.

Here's a code I've written so far. I tried to add an active cell formula to this to allow the macro to work on any column but had even worse results. Any help would be greatly appreciated.


	VB:
	
 FillEmpty() 
    Dim rCell, rColumn As Range 
    Dim lLastRow As Long 
     
    lLastRow = Range("Y65536").End(xlUp).Offset(0, 0).Row 
     
    Set rColumn = Range("Y1:Y" & lLastRow) 
     
    For Each rCell In rColumn 
        If IsEmpty(rCell) Then 
            If rCell.Offset(0, -25).Value  Empty Then 
                rCell.Formula = "=SUM(J:X,rCell.row)" 
            Else 
                 
            End If 
        Else 
             
        End If 
    Next rCell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you,

Marcus

I am trying to post the results of one cell to the next blank cell in a different row. For example; I need the results in S3 to post to the next blank cell in row P. I tried the worksheet_change code below but that will only work if I make a manual entry in S3 and does not work if I use a formula in S3 to perform the calculation. My formula in S3 is =MAX(L5:L505). When I hit F9 to recalculate I need the results of S3 to post in row P each time. Perhaps there is a formula that I can use as opposed to VBA.


	VB:
	
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.Address  "$S$3" Then Exit Sub 
     
    Application.EnableEvents = False 
    Range("P65536").End(xlUp).Offset(1, 0).Value = Target.Value 
    Application.EnableEvents = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to take an average of a list of data...But some of the cells are blank. I want to include the empty cells in my average formula range because depending upon other criteria the empty cells will be populated. However I don't want the Average formula to include the blank cells in the average when they are blank. Is there a way that I can do an IF formula combined with the Average formula that will only include cells when they have data in them? I have tried the AVERAGEIF but it doesn't work this way.
Thanks

I have problems with conditional formating registering blank cells as having the value 0. This means I can't set 0 in the range of any conditions as it flags blank cells! I'd appreciate any help you can give on this...

Hi

I would like to write a macro that would copy formula in B2 and then copy it down row B until it finds that a cell in row A is blank. This report changes all the time so I never know how much data is in row A. So I want the macro to stop copying the formula down row B when it finds a blank cell in Row A.

I tried the Do Until but it did not do anything unless I had something wrong. I recorded the intial copy B2 to B3 to see what it wrote. I tried an if statement also.

Any help would be apprieciated. I have Excel 2003.

Currently I am struckling with advanced filtering. In my criteria range
one cell contains an IF formula and the false condition it returns an
empty cell "". Problem is now that advanced filter does not treat this
as a true blank cell; it now only matches with strings. So if I have
figure in this column of my database, it is not selected. I would like
to return a true blank from my IF formula, so that everything is
matched both strings and figures. Is this possible?

Bye
Joop

I'm looking for a function or formula in Excel 2003 which will return the
last non-blank cell in a range. The values in the range are numeric;
however, they're not sorted in numeric order. I could probably figure out
how to write a macro, but I'd prefer a formula. Any suggestions?

Thanks.

Hi all,

I want to retrieve the value for the 2nd, 3rd and 4th non-blank cell in the same row.

I have used =MATCH(TRUE,LEN(B3:Q3)0,0) to retrieve the correct column index for the first non-blank cell and I have used =INDEX(B:Q,2,R3) to retrieve the value but I am having trouble locating a formula to retrieve 2nd/3rd and 4th.

I have attached an example - any help would be appreciated.

AmiK

I got myself in a bind. Attached is an example of my problem.

I learned that Excel will allow the use of blank cells in mathematical operations. However, it won't allow a cell that appears blank but has a formula in it to also be used in mathematical operations. See cell B3 and you'll see what I mean. Is there a way to make it work? Many thanks.

Currently I am struckling with advanced filtering. In my criteria range
one cell contains an IF formula and the false condition it returns an
empty cell "". Problem is now that advanced filter does not treat this
as a true blank cell; it now only matches with strings. So if I have
figure in this column of my database, it is not selected. I would like
to return a true blank from my IF formula, so that everything is
matched both strings and figures. Is this possible?

Bye
Joop

First time here. I'm new to excel, so this will probably be very simple for most of you, but I have been trying to find an answer for hours. Any help would be much appreciated!

I need to know the value (which is a date) of the first non-blank cell in a range in a row. I have found some array formulas by searching forums/google etc., but they either return it for the whole row (which I don't want), or they return text (which I don't want), or they don't work at all.

The range is: B9:FL9. Some of the cells in this range are blank, some are "0", and some contain dates. I need to know the most recent (furthest left) cell that contains a date and what that date is. Thanks!

Is there a way to include an if then else function to an average formula that will skip cells with zero?

I went into better detail in the attached example.


No luck finding an answer? You could always try Google.