Help with Excel Use Case scenario (Function)

vpandey4

New Member
Joined
Jun 7, 2022
Messages
6
Office Version
  1. 365
  1. I have a set of data where we get work done by every employee on a weekly basis (eg. E01, E02) in man-days
  2. I use that data to derive the velocity data set- (Work Done/5) and use it to derive the average velocity trend for coming weeks (using AVERAGE function)
    1. Employee list is selected via drop down and INDEX MATCH function used to find the respective work done for the week
  3. If there is a particular week which I want to ignore from average velocity calculation for a specific employee (eg. E01), we capture it in a separate list
    1. based on ignore list- I want to capture NA() as a velocity for that week (when respective employee is selected) --> This is where I am having trouble to write the function for the same
    2. I use AVERAGEIF(range,"<>#N/A") to calculate the trend
Please suggest.

Book1
ABCDEFGHIJKLMNO
1SELECTIONCALCULATED VELOCITYINPUT DATAIGNORE LIST ENTRY
2E01E02IGNORE LIST
3E01DATEVelocityWEEKWORKDONEWORKDONEEmpDate
408/06/2022108/06/202255E0115/06/2022
515/06/20220.515/06/20222.55
622/06/20220.822/06/202248
729/06/20220.7729/06/2022
806/07/20220.7706/07/2022
913/07/20220.7713/07/2022
1020/07/20220.7720/07/2022
1127/07/20220.7727/07/2022
1203/08/20220.7703/08/2022
1310/08/20220.7710/08/2022
14
Sheet1
Cell Formulas
RangeFormula
E4:E6E4=INDEX($I$2:$K$13,MATCH($D4,$I$2:$I$13,0),MATCH($B$3,$I$2:$K$2,0))/5
D5:D13,I5:I13D5=D4+7
E7:E13E7=AVERAGEIF($E$4:E6,"<>#N/A")
Named Ranges
NameRefers ToCells
sel=Sheet1!$J$2:$K$2E4:E6
Cells with Data Validation
CellAllowCriteria
B3List=sel
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Got it working on my own using "=COUNTIFS($M$4:$M$8,$B$3,$N$4:$N$8,$D4)":

Book1
ABCDEFGHIJKLMNO
1SELECTIONCALCULATED VELOCITYINPUT DATAIGNORE LIST ENTRY
2E01E02IGNORE LIST
3E01DATEVelocityFormulaWEEKWORKDONEWORKDONEEmpDate
408/06/20221108/06/202255E0101/06/2022
515/06/20220.5#N/A15/06/20222.55E0115/06/2022
622/06/20220.80.822/06/202248E0222/06/2022
729/06/20220.770.8229/06/2022
806/07/20220.770.8106/07/2022
913/07/20220.770.8113/07/2022
1020/07/20220.770.8020/07/2022
1127/07/20220.770.8027/07/2022
1203/08/20220.770.8003/08/2022
1310/08/20220.770.8010/08/2022
14
Sheet1
Cell Formulas
RangeFormula
E4:E6E4=INDEX($I$2:$K$13,MATCH($D4,$I$2:$I$13,0),MATCH($B$3,$I$2:$K$2,0))/5
F4:F6F4=IF(COUNTIFS($M$4:$M$8,$B$3,$N$4:$N$8,$D4)=0,INDEX($I$2:$K$13,MATCH($D4,$I$2:$I$13,0),MATCH($B$3,$I$2:$K$2,0))/5,NA())
D5:D13,I5:I13D5=D4+7
E7:F13E7=AVERAGEIF($E$4:E6,"<>#N/A")
Named Ranges
NameRefers ToCells
sel=Sheet1!$J$2:$K$2E4:F6
Cells with Data Validation
CellAllowCriteria
B3List=sel
 
Upvote 0
Solution

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top