Match Formula

[Jim]

Hello,

In A1 I have a date (this will be the end date for a commission cycle of 14
days)

In B1 I have my name (for this formula we will call me Jim)

In column E I have dates for the last year. In the F column I have the
names of my sales people.

in columns G:J I have data.

I need help with a formula that will match the name (B1) as well as average
column G for the date in A1and the 13 previous days.

Thank you in advance for the help. I appreciate your time.

 

[Mike H]

Hi,

In B1 I have my name (for this formula we will call me Jim

Where does 'Jim' figure in this formula? where do we match that? Do we only
average those of the last 13 days where 'Jim' appears in a row?


Mike

 

[Jim]

I ned to match the name as well as the dates

Hi,


Where does 'Jim' figure in this formula? where do we match that? Do we only
average those of the last 13 days where 'Jim' appears in a row?


Mike

 

[Mike H]

Hi,

You still didn't tell me in which column we find 'Jim' so I'm guessing at
column F

=AVERAGE(IF(E1:E365>=A1-13,IF(F1:F365=B1,G1:G365)))

Where
A1 is your date
B1 contains Jim
Jim appears in Column F

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

 

[Mike H]

OOPS,

Missed a condition, try this instead

=AVERAGE(IF(E1:E365>=A1-13,IF(E1:E365<=A1,IF(F1:F365=B1,G1:G365))))

Still an array so enter as shown in previous post.

Mike