My first Excel Guru Thread

wade moore · 10-20-2007, 03:55 PM

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.

Buccaneer · 10-20-2007, 04:52 PM

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

wade moore · 10-20-2007, 06:31 PM

Quote:

Originally Posted by Buccaneer

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.

gottimd · 10-20-2007, 06:40 PM

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.

Code:

A             B        C
1/1/2007    5     =IF(AND(A3>A1, A3 < A2), " Y ", " " )

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.

FrogMan · 10-20-2007, 07:22 PM

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:

Code:

=SUMIF(A2:A32000,AND(>A1, < B1),B2:B32000)

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...

Bonegavel · 10-20-2007, 08:27 PM

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

wade moore · 10-20-2007, 10:26 PM

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

.

gottimd · 10-21-2007, 04:31 PM

Quote:

Originally Posted by Bonegavel

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

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

wade moore · 10-21-2007, 08:01 PM

I like the cozy comforts of FOFC

.

10-20-2007, 06:40 PM	#4
gottimd Dearly Missed (9/25/77-12/23/08) Join Date: Nov 2003 Location: DC Suburbs	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. Code: A B C 1/1/2007 5 =IF(AND(A3>A1, A3 < A2), " Y ", " " ) 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. __________________ NAFL New Orleans Saints GM/Co-Commish MP Career Record: 114-85 NAFL Super Bowl XI Champs In memory of Gavin Anthony: 7/22/08-7/26/08 Last edited by gottimd : 10-20-2007 at 06:45 PM.

10-20-2007, 07:22 PM	#5
FrogMan Hattrick Moderator Join Date: Jan 2003 Location: Pintendre, Qc, Canada	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: Code: =SUMIF(A2:A32000,AND(>A1, < B1),B2:B32000) 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 Black Belt is a White Belt who refused to give up... follow my story: The real life story of a running frog... Last edited by FrogMan : 10-20-2007 at 07:53 PM.

10-20-2007, 08:27 PM	#6
Bonegavel Awaiting Further Instructions... Join Date: Nov 2001 Location: Macungie, PA	A great place I've found for excel help is ozgrid.com __________________

10-20-2007, 04:52 PM	#2
Buccaneer Head Coach Join Date: Oct 2000 Location: Colorado	I don't know what would work exactly but always put the criteria in quotes.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)