My first Excel Guru Thread
 

Ok, i'm pretty good with Excel - but this thing I'm trying to do for work is killing me..

Here's what I'm trying to do.. I have a whole bunch of data (trouble tickets). I want to calculate the average of one column, but do it only for a range of dates.

So, I have something like this:

Date |Number
1/1/2007 |5
1/2/2007 |8
1/20/2007|9
2/15/2007|12

But, i only want to average everything from 1/1/2007 - 1/15/2007.

I've tried utilizing SUMIF and COUNTIF, but if that's the route I can't figure it out. So something like =SUMIF(A2:A32000,AND(>1/1/2007,<1/15/2007),B2:B32000) - clearly that doesn't work though, because AND won't take arguments like that.

I'm really not sure where i can go with this - so i thought I'd reach out to FOFC.

I have to go out for a bit, but when I come back I'll probably work on this some while watching football.

I don't know what would work exactly but always put the criteria in quotes.

Yeah, sorry, that was just sloppy translating (I was actually doing the work on another PC)...

Either way I think it doesn't work because it's not a full statement.. like.. B1>B2 for the AND() Function.

Maybe additional work but lets say you put in cell A1 the beginning date and cell A2 the end date that you want to use in the calculation.


Then, add a column (like C3 above) after the number of trouble tickets that says something like " =IF(AND(A3>A1, A3 < A2), " Y ", " " ) "

So now you can change the dates as a variable. Then use the Sumif divided by a Countif formula for all the values in Column C that = "Y".

Again, its a long way of doing what you are trying to do, but it will work.

I think your argument should be the serial number represented by the date.

for example, 1/1/2007 is 39083. You can find it by entering that date in a cell and changing the number format of said cell to "General".

Either that, or write the date to be used as argument in cells of their own, like for your example:


where your first date is in A1 and your second date is in B1

FM


edit, there shouldn't be a space between < and B1 but vbulletin was eating the formatting without one...

A great place I've found for excel help is ozgrid.com

I figured out a way around this that was similar to what gottimd was suggesting.. I'll explain it more in detail tomorrow ;).

The Free message board at mrexcel.com is great. It has Excel and access help.

I like the cozy comforts of FOFC ;).