How to pull values using dates?

[TurkeyFeast]

I have data values for given dates and I want to create a formula that will pull the value from column B based on the date in Column A. However, I would like to have the inconsistently formatted data in Column A be organized by month. Is this possible?

the formula I am using for "avg cars per month" is: =IF(MONTH(A4)&MONTH(A5)=MONTH(C4),AVERAGE(B4:B5),IF(MONTH(A4)=MONTH(C4),B4,"false"))



[TABLE="width: 384"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Cars
[/TD]
[TD]Month
[/TD]
[TD]Avg Cars per month
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/4/11
[/TD]
[TD]50
[/TD]
[TD]1/1/2011
[/TD]
[TD]50
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/1/11
[/TD]
[TD]60
[/TD]
[TD]2/1/2011
[/TD]
[TD]60
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/1/11
[/TD]
[TD]60
[/TD]
[TD]3/1/2011
[/TD]
[TD]60
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/25/11
[/TD]
[TD]42
[/TD]
[TD]3/1/2011
[/TD]
[TD]42
[/TD]
[TD]<- error, should be 51
[/TD]
[/TR]
[TR]
[TD]4/5/11
[/TD]
[TD]67
[/TD]
[TD]4/1/2011
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/25/11
[/TD]
[TD]69
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/18/11
[/TD]
[TD]63
[/TD]
[TD]5/1/2011
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/20/11
[/TD]
[TD]64
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/6/11
[/TD]
[TD]66
[/TD]
[TD]6/1/2011
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/10/11
[/TD]
[TD]76
[/TD]
[TD]7/1/2011
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/18/11
[/TD]
[TD]51
[/TD]
[TD][/TD]
[TD][/TD]
[TD]




[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!
Why is the row above it (the first record for March) showing 60? Wouldn't you want that record to show 51 as well?
If not, then maybe this is what you are looking for:

=IF(AND(MONTH(A4)=MONTH(C4),MONTH(A5)=MONTH(C4)),AVERAGE(B4:B5),IF(MONTH(A4)=MONTH(C4),B4,"false"))

What about the possibility of having more than two records for any month? Is that possible?

 

[Marcelo Branco]

Welcome to Mr Excel forum

Not sure i understand what you trying to do. See if this does what you need

Formula in D2 copied down
=AVERAGEIFS(B$2:B2,A$2:A2,">="&DATE(YEAR(A2),MONTH(A2),1),A$2:A2,"<="&EOMONTH(A2,0))

Hope this helps

M.

 

[TurkeyFeast]

Welcome to Mr Excel forum

Not sure i understand what you trying to do. See if this does what you need

Formula in D2 copied down
=AVERAGEIFS(B$2:B2,A$2:A2,">="&DATE(YEAR(A2),MONTH(A2),1),A$2:A2,"<="&EOMONTH(A2,0))

Hope this helps

M.

This equation worked great except for one problem. The data in column A expands farther down than months in column D. Is there an easy way to manipulate the equation or reformat the dates in column D without having to go thru one by one and move the dates down?

Thank you in advance!

 

[Marcelo Branco]

This equation worked great except for one problem. The data in column A expands farther down than months in column D. Is there an easy way to manipulate the equation or reformat the dates in column D without having to go thru one by one and move the dates down?

Thank you in advance!

I'm not following you. The formula i have suggested is to be placed in column D and uses only data in columns A and B - column C (months) is not needed.

M.

 

[TurkeyFeast]

I'm not following you. The formula i have suggested is to be placed in column D and uses only data in columns A and B - column C (months) is not needed.

M.

Lets say that the data in column A runs all the way down to row 100 - because some months have conducted more than 1 survey. When I place your equation into column D, it will only perform the correct calculation for the number of months I have (lets say 60 months or 5 years). So column A goes down to row 100 but column D, with the equation, only goes down to row 60. See the issue here?

This would be much easier to explain, but I cannot post a snapshot..

 

[Marcelo Branco]

Still confused. Couldn't you copy/drag down the formula in column D up to row 100?

M.

 

[mart37]

You can use a pivot table.

 

[Marcelo Branco]

@TurkeyFeast

I've read your PM. Private support is not recommended in this forum.

Try to post a data sample here, in the thread, along with expected results, so others members also can help.
There are several ways to post a data sample. See section B in
Guidelines for Forum Use

M.

 

[TurkeyFeast]

Thank you for the update.

Haha, I was making a silly mistake and changed the values in your formula to stretch the entire length of the data. final working formula is:

=AVERAGEIFS(C5:C346,B5:B346,">="&DATE(YEAR(X6),MONTH(X6),1),B5:B346,"<="&EOMONTH(X7,0))

**column A is B in the formula. Column B became C. Column D became X. only big difference was changing the ranges.

Thanks for all of your help!