Hi everyone...I'm back, again.
Could someone have a look at the attached spreadsheet and let me know where Im going wrong? Im trying to sum and average bat calls per hour. I applied the formulae and they seem to work ok but the sum of column "D" and the sum of column "I" do not have the same value, so some data is not being included for some reason. Therefore I dont trust the averages either.
I'd appreciate any ideas, thanks!
Help2.xlsx
From cell G2 to G17 you have Time values. Below that you have Date & Time values. Change those to Time values.
And use this in J2 and down:
=IFERROR(AVERAGEIF($B$2:$B$320,G2,$D$2:$D$320),0)
Last edited by Cutter; 05-13-2012 at 10:07 AM.
Thanks for your help Cutter, that's sorted the problem out! Really appreciate your time
For future though, how would I go about changing the time and date field to just time? The cell formats were the same for the whole column, i.e. they were all "time" but those cells you mentioned did have the date as well, only visible in the forumla bar. I did it manually this time by deleting each date from the formula bar but if the data set gets bigger this may not be feasible?
Also what does the IFERROR formula do? I can understand it giving a 0 result for averages where there is no data to average, but in some rows it gave a result where before there was a error result. Just wondering why it would then give a result as opposed to a zero?
The IFERROR() function was introduced with the release of Excel 2007. It replaces the need to duplicate a formula within a formula to account for anticipated errors or otherwise check for conditions that would lead to an error. In essence it tells Excel to calculate your formula and give the result but if that formula results in an error give a specified result instead of showing the error.
Here is a link: http://www.excelfunctions.net/Excel-Iferror.html
As for the Date/Time problem. It occurred because you dragged the time values down. So you were adding 1 hour to the previous cell value as you dragged down. To Excel one hour is equivalent to 1/24 (because one day to Excel has the value of 1). Once you reached midnight Excel reached 1.0 and then added 1/24 to it.
Last edited by Cutter; 05-13-2012 at 10:42 AM.
Ok thanks for your explanation!
You're welcome. Thanks for the 'star tap'.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks