|
Match Formula
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. |
Match Formula
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" wrote: 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. |
Match Formula
I ned to match the name as well as the dates
"Mike H" wrote: 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" wrote: 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. |
Match Formula
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 "Jim" wrote: I ned to match the name as well as the dates "Mike H" wrote: 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" wrote: 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. |
Match Formula
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 "Mike H" wrote: 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 "Jim" wrote: I ned to match the name as well as the dates "Mike H" wrote: 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" wrote: 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. |
| All times are GMT +1. The time now is 11:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com