Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Average Calculation To Exclude Blank Cells

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi,

I have an array formula that calculates the average of numbers between two dates:

{=AVERAGE(IF('Date Range'!$B:$B>$H$4,IF('Date Range'!$B:$B<=$B$3,'Numbers to Sum'!$C:$C)))*100}

The start date is the day after the date in H4 and the end date is in cell B3. The dates are in column B and the numbers to sum associated with these dates are in column C.

Some of the cells in column C are blank and my formula is taking these blanks into account in calculating the average, while I would prefer not to count them in the calculation.

Can someone please suggest how I could amend the formula above to accommodate this?

Thanks!

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Pop-Up Message Box When a Range of Cells Reaches a Certain Average
- This macro will display a message box when the numbers within a range of cells reaches an average of 5. The current mac
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format

Similar Topics







Hi All,

I need a help on Average formula .i.e. I want to find the average of numbers present in a column based on multiple criteria. Here is what I have. Column A has Category .i.e. Deliver and Receive, Column B has Dates, Column C has Amounts. and I want to get the average of Amounts between two dates for each category separately for e.g. row I9 has Start date, row J9 has end date and L9 has Category so I want the average formula to look into column A for category first then in column B the start date first and then the end date and take the average of all the numbers which are there in column C .i.e. Amount column.

Thanks a lot for your help in advance.


This has been bugging me for 3 days and wondering if anyone can help me. If it's not possible, I just want to know so that I can stop trying, but it seems like it 'should' work.

The data:
There are a number of columns, but we'll use the first 2 first. Column A has the dates from 1/1/2009 to 12/31/2009. Column B will has numbers, mainly 0-100. One cell will have a 'report date' which is just a date.

The formula needed:
I have a few cells labeled wk1, wk2, wk3. Week 1 will start with the 'report' date. wk1 I would like the Average of Column B IF the dates in Column A are within the 'wk'. wk1 = report date TO report date + 6. wk2 = report date + 7 TO report date + 13 and so on.

My goal is to have the formulas all set so that when we move to another report date and the raw data is added, I can just change the 'report date' and everything (the wk numbers) will automatically update with the new raw data.

The furthest I got was this, but it didn't work.
=AVERAGE(IF('A3:A33>=VALUE(D3)

Hi All !

I need an average formula which should work in a range of cells & to calculate average based on date for example I used formula to calculate average of previous day as

=AVERAGE(IF(B251:B351=TODAY()-1,R251:R351))

But it is not picking up the average value in a range. Dates are in column B & averages for date are in column R.

Can anyone suggest where it is wrong or suggest me another formula.

Regards


Hi again Everyone,


I have a datasheet (that is not created by me, and I cannot change it) with a column of proposal dates and a column of start dates. I am creating a report from this sheet.

I have a great Array that was given to me here that works very well to average the number of days between the dates, if the cell is not blank.

=AVERAGE(IF('Test Data'!$C$2:$C$65535-'Test Data'!$S$2:$S$65535>0,'Test Data'!$C$2:$C$65535-'Test Data'!$S$2:$S$65535))

My problem is that now I've found out some of the date cells in the datasheet may be blank. This throws my averages completely off. Is there a way to manipulate my array so it ignores blank cells in either column C or S? Meaning, that if there are blank dates in either of those columns, that it will skip that cell in its calculation?

I really appreciate any help. Let me know if you need more clarification


Hi, for my work I have to calculate the average of cases for a year and the data is set up with monthly average in the row. I also need to not incorporate the cells with 0 or a dash in them because they are referenced to another worksheet with formula. The data is set up this way:

dates Cases
1/08 1
1/15 3
1/25 4
1/30 2
1st period: =IF(SUM(B2:B5)>0,AVERAGE(B2:B5),0)
(average)
. ((the next 11 mos. are repeated)
.
.
12/1 -
12/8 -
12/16 -
12/23 -
12/30 -
12th prd: =IF(SUM(B76:B79)>0,AVERAGE(B76:79),0)
(average)

Average: (Average up to the date)


At the end, I want to calculate the average "up to date", but I'm having problem selecting range with just the subaverage cells because Excel won't let me. Can you please help me calculating the average up to date?

Thank you!


Hi I'd like to ask a cell to calculate an average of data (numbers) in a column.

I'd like to be able to insert rows to increase the column length accomodating new data as it comes in.

Also if I clear a cell in the column from a previous quantity to zero, I want it to be left our of the average; that is zero does not equal a zero value instance, and should not count as an increase in the denominator of the average; that is not 0/1 but rather as if it did not exist.

I can make a zero cell blank but would rather allow either "0" or blank to not count in the average.

Finally and this would be so cool I'd like the average to refer to a column that accounts the date of the entry, and for the avergae to filter for only one week at a time ending on Fridays.

If that would be possible I'd love to have a pop up calendar in my date column, and if that helps to make this easier to then I'd need to know how to do that. Currently I hand enter dates nn/dd/yy

Thanks


I have a worksheet with Column A containing approx. 40 different dates. In column B, C, D there are numbers (ex: 7250, 8400, 7390)
Is it possible to have excel sort by date, take all the numbers for that date and compute and average. Keep in mind there may be some rows with the same date. For example Row 3,4,5,6,7 all are dated 4/11/09. Excel would add the 15 totals found in B,C,D and tell me that the average for 4/11/09 is 7680. Thanks.


I am using Excel 2003. I wish to have the ability to average a column of numbers based on a specific date. Both the dates and numbers associated with the dates are listed vertically. On any given day, I could have two numbers, three numbers, or four numbers that I need to average - it varies. For example, in the data set below, I need a function that will see "1/1/09" and retrieve an average for 500, 650, 575, and 700 (=606.25). Then on 1/2/09 it will retrieve an average of 550.

Column A: Column B
1/1/09 500
1/1/09 650
1/1/09 575
1/1/09 700
1/2/09 675
1/2/09 425
1/2/09 550

Please assist me.
Thank you


I am trying to figure out a formula to average 5 cells in column B. Column A is date in ascending order. Cell C1 is the date reference to search values in column B. However, the date in C1 is the middle date of the 5 dates which should be average. That means if C1 is 2011/1/3, the date range to be included will be 2011/1/1 to 2011/1/5. One more criteria is that the range should be expanded if there are dates with 0 value. That means if value on 2011/1/4 is 0, the date range should be 2011/1/1 to 2011/1/6.

Thanks for help.


I am trying to figure out a formula to average 5 cells in column B. Column A is date in ascending order. Cell C1 is the date reference to search values in column B. However, the date in C1 is the middle date of the 5 dates which should be average. That means if C1 is 2011/1/3, the date range to be included will be 2011/1/1 to 2011/1/5. One more criteria is that the range should be expanded if there are dates with 0 value. That means if value on 2011/1/4 is 0, the date range should be 2011/1/1 to 2011/1/6.

Thanks for help.


I have a workbook with a date on worksheet 1 that indicates data has
been entered for that month.

On worksheet 2 is are columns of numbers with an average for each
column at the bottom. I would like the average to use the date on
worksheet 1 to calculate the average.

The start of the range is known, but the end of the range is found only
by looking up the date on worksheet 1.

I have tried to use something like: =AVERAGE(B10:ADDRESS(VLOOKUP(date,
range, 1, TRUE). This doesn't work and I can't find the functions
needed to make it successful.

Thanks in advance for any help!




I'm trying to do a fairly simple calculation, but I'm getting stuck.

I have two columns of data, the first column indicates the category, the second column indicates the dollar value. For instance

1 45
2 40
2 20
1 20
1 35
1
2 60
3 54
2 85
3 65
3 54

All I want to do is take an average of the second column based on the categorization of the first column. For example, average of all category 3's ((54+65)/2). I have a basic array function set up, but, the problem is that not every row has an associated dollar value. When the array formula calculates the average, it counts a blank cell as a zero, not a blank. Example: Average of category 1 is ((45+20+35+0)/4) not ((45+20+35)/3). I don't want the average to include the zeros...it is making all my averages artificially low. If the second column is a blank, I want the array formula to not mix that row of data into the calculation.

Any suggestions on how to get around that?


Hello everyone, I'm new to this forum, but it looks like the right place to come asking excel questions. This problem has been bothering me now for quite some time and I'd hugely appreciate any advice, as I've run out of ideas.

I'm trying to calculate the average number of days between various sets of dates. For example, a computer repair shop wants to know on average how often Computer A comes in. The dates it's been in so far are w,x,y,z and now he wants to calculate the average of those.

I know, that's easy enough. All divided by 4.

But what I'm after is the ability to then add more dates without having to alter the code. So if Computer A returns and we now have five dates, the formula works from the first date until it finds the last date.

I sure hope this is making sense...

I've tried Average, Count IF, Sum, Days360, even VBA.

Please, please somebody help me...

Many thanks.

OzM.


Okay, so here are two, nearly exactly same array formulas

=AVERAGE(IF(WEEKDAY(A1:A14,2) < 6,B1:B14))
=AVERAGE(IF(WEEKDAY(A1:A14,2) < 6,B1:B14,))

See the difference? The comma before the final closing parens on the 2nd formula. Perfectly legal, perfectly correct syntax. But apparently not in an array formula.

Do this, in column A, starting in cell A1, enter a date, then use your handle and drag down so you get a sequential list of 14 dates (ending in cell A14)

In B1, enter value: 1
In B2, 2
Now select those two and drag down to B14. You'll have numbers 1 thru 14.

Now put both array formulas in blank cells. (Remember CTRL-Shift-Enter)
The first one correctly answers the average is 8.5
The second one incorrectly states the average is 3.035714

Why does the perfectly legal comma skew the result!?

(What the formula does it check for dates that are not Saturday or Sunday. I needed the average of activity for weekdays only.)


I am trying to figure out a formula that will choose values based on a certain date range. So it will review a column of data (lets say column "A") and only use the data from that column IF Column B (which has all the dates in it) is equal to a certain start and finish date (which are two other cells).
I am a real estate appraiser, and am trying to pull out certain data from a larger data set so that I can see average, hi, low, and medians. But I need to sort it into 12 month periods (the most recent, the previous 12 month period, and the 12 month period before that (3 years total). So I want to get (for example) the average asking price of all homes in my 3 years worth of data that occurred only in the period 12 to 24 months ago. To do that, the formula would look at the two cells that state the start and end date of the 12 month period I need, then search column B to see which dates are relevant, and then if the date is relevant it uses the data in column A to (for example) determine the average.

This is what I tried:
=IF(S2:S1498>E1505,AND(S2:S1498<F1505),AVERAGE(G2:G1498))

where "S" is the date column, E1505 & F1505 are the start and end dates, and "G" is the column with the data I am trying to average.

Any help would be appreciated!


Hi,
I have a set of dates with values that I'm trying to create a 5day average over. If there are no holidays, then I just want to average the last 5 dates (therefore the last 5 cells in my spreadsheet)

however, if there was a holiday in the last 5 days, then the value on that date is blank, so i need to average over the last 6 cells . If there were two holidays, I need to average over the last 7 cells, etc.

Is there a way to do this? ie have a range that expands if one of the cells in it is blank?

tx!!


At work I just started to do more administrative things and I am managing contract were we process orders daily and I need to give an average completion date, yet I was wondering if there is a formula to get an average from start date to end date. I entered the dates using =DATE formula, yet I was trying the SUMIF/COUNTIF route. Thanks in advance!!


Hi there,

can you please help me try to calculate the average days between 3 dates?
SO in columns A,B, & C are date cell inputs and in column D should display the total average between the 3 dates in Days..

example..
Column A Column B Column C Column D
24/02/2009 25/02/2009 27/02/2009 [Ave in days]

I tried '=Average(day(A1:A3))' but i keep getting error '#VALUE'..

Tell me what am I doing wrong? or better yet can you suggest a better formula?

Thanks


Thanks for your help.

Currently i am working out the time between 2 dates, the earlier date is located in column M and the later date is located in column V.

I am using the datedif function to get the time difference and this is in column AK and is in days.

I am then averaging the entire column AK to get me overall average.

The dates start in 2007 and are constantly being added too.

What i need to do is get a yearly average for 2007, a yearly average from 2008 and then monthly averages from 2009 to present (and from now on)

As the difference between the dates can be up to two months i want to count the later date as the one that you judge the month by.

For example if the first date was on the 14th of June and the last on the 12 of September it the 60 day difference would go into the september average.

I really hope you can help because i cant see a really simple way of doing it.

Thanks for your help and i look forward to hearing from you.


I am looking to do some calculations (such as average, min, max, etc) on every other row of a column of numbers in a in a worksheet that I plan to add rows to in the future.

In my case I have a sheet with week-to-date numbers followed by year-to-date numbers, then followed by another week-to-date number, then year-to-date and so on. I want to determine the average, etc of say the week-to-date numbers.

When I just had the week-to-date numbers in a table alone I used the AVERAGE and MIN and MAX function formula to calculate the results nicely. But obviously this won't work with extra data every other row.

Is there a way to still use these functions somehow and ignore every other row? Or some other way considering that the number of rows will be changing as the years go by, and I don't want to have to update the formula as I add data? Thanks




Hi,

I would like to create a formula to calculate the linked growth rates (columns B & C) of two products between two dates.

The start and end dates are shown in F2:F3 and the formula to calculate the linked growth rates is in F6:G6. Values will be included in the calculation if their associated date in column A is greater than the start date and less than or equal to the end date.

I would like to amend the formula in F6 so that it takes into account both the start and end dates in F2:F3 and also the product name in F5.

Can someone please suggest a solution.

Thanks very much!

I am attempting to find the average of a range of numbers in column C that meet the criteria of two sets of data in Column A and Column B - I am using the following:
=AVERAGE(IF($A$2:$A$19=6,IF($B$2:$B$19=4,$C$2:$C$19)))

This should return an average of the numbers in column C that meet the criteria in Columns A and B. Except that Column C has some blank cells that are being included in the average as "0" and returning the wrong average. What do I need to add to the formula so that the blank cells in column C are not calculated in the average?

Thank you for your help.


i want to calculate averge based on given range of Dates wihin the month

e.g

Date Product A Product B
30-Jul-09 , 2 ,56
31-Jul-09 , 5 ,456
1-Aug-09 , 6 ,8
2-Aug-09 , 8 ,7
3-Aug-09 , 7 ,7
4-Aug-09 , 69 ,8
5-Aug-09 , 2 ,66
6-Aug-09 , 1 ,44
7-Aug-09 , 6 ,55
8-Aug-09 , 6 ,66
9-Aug-09 , 13 ,66
10-Aug-09 , 14 ,55
11-Aug-09 , 14 ,10

Start Date 30-Jul-09 Formula to return average within these dates ?
End Date 30-Aug-09


I have 2 columns of data.

A1:A100 contain dates with some blanks. Some of the dates are TODAY() and the others are all dates from the past year.

B1:B100 contain numbers of days.

I want to get the average number of days (column B) for those items where the date does not equal today.

Many thanks.


I have a spreadsheet that keeps track of dozens of dates for all of our projects. Each 'project step' has a time calculation that gives the number of days that step took dependent on the start/finish dates. These colums are averaged at the bottom with an AVERAGE(A:A) cacluation so we can continue to add rows and have their data automatically added to the average calculation. Also below this row is a standard deviation calculation using the same format.

What I would like to do is add a cell below the average/stdev rows that counts how many projects have completed that step...but I don't want that number included in my average/stdev calculations.

How would I go about making an AVERAGE/STDEV(A:A) calculation take everything above its containing cell into account but nothing below its containing cell?