krispatterson
Board Regular
- Joined
- Apr 28, 2017
- Messages
- 51
My main problem / lack of knowledge with Excel is getting formulas that look at multiple things to work. I really struggle with it... I've got 2 different formulas, but have no idea how to make it work as one...
data is as follows:
<tbody>
</tbody>
Individual formulas I'm using are:
=COUNTIFS(A:A,">="&D1,A:A,"<"&EDATE(D1,1),B:B,"chris")
This counts how many instances of "chris" occur in a month date range.
=AVERAGE(C:C)
This averages the percentages of C.
What I'm trying to achieve in E1 is a formula that:
> looks at all dates in 12/16
> filters for just "Chris" / "Joe"
> averages out the column C
So, results:
> E1 will be 1.5%
> F1 will be 1%
> E2 will be 1.33%
> F2 will be 1.25%
Is that possible? Thanks :D
data is as follows:
A | B | C | D | E (Chris) | F (Joe) | |
1 | 05/01/17 | Joe | 1% | 01/12/16 | ||
2 | 15/01/17 | Chris | 1.25% | 01/01/17 | ||
3 | 12/12/16 | Chris | 1.5% | 01/02/17 | ||
4 | 02/01/17 | Chris | 1.5% | |||
5 | 19/12/16 | Joe | 1% | |||
6 | 22/01/17 | Joe | 1.5% | |||
7 | 27/01/17 | Chris | 1.25% |
<tbody>
</tbody>
Individual formulas I'm using are:
=COUNTIFS(A:A,">="&D1,A:A,"<"&EDATE(D1,1),B:B,"chris")
This counts how many instances of "chris" occur in a month date range.
=AVERAGE(C:C)
This averages the percentages of C.
What I'm trying to achieve in E1 is a formula that:
> looks at all dates in 12/16
> filters for just "Chris" / "Joe"
> averages out the column C
So, results:
> E1 will be 1.5%
> F1 will be 1%
> E2 will be 1.33%
> F2 will be 1.25%
Is that possible? Thanks :D