Hello,
I'm creating an associate scorecard which will display the percent of items rejected for a specific transaction type in a given month, on a 12 month rolling basis. The tab the formula will go on uses an indirect formula to display one or all associates,
one or all managers and one or all executive managers.
The formula pulls from 2 different worksheets, On the Error_Detail tab column N idenfitifes the transaction type, column T identifies the month. On the SR_Detail tab column N identifies the trans type, column U identifies the month the trans was performed
in.
I wrote the formula to first identify the number of errors by associate, month and transaction type on the Error_Detail tab then divide that number by the total number of items for all associates for the same month and transaction type on SR_Detail
tab
|
Formula:
IFERROR(COUNTIFS(Error_Detail!$N:$N,$A$9,Error_Detail!$T:$T,TEXT(Q$4,"MMM-yy"),INDIRECT($S$1),INDIRECT($T$1))/COUNTIFS(SR_Detail!$N:$N,$A$9,SR_Detail!$U:$U,TEXT(Q$4,"MMM-yy"),INDIRECT($N$1),INDIRECT($O$1)),0)
I'm by no means an expert when it comes to the more complicated formulas. Am I way off on this? Is there a better more simple formula?
|
|
May 26th, 2015 3:04pm
Hi
Lorac,
According to your description, this issue is more related to formula in excel, the Excel IT Pro Discussion forum is a better place for this issue, we will move it there for you.
Regards
Starain
May 26th, 2015 11:10pm
Hi Lorac,
Based on your description, I cannot reproduce your excel environment. In your formula you quote a Q4 cell, but you didnt explain what the value of Q4 is (A9 and S1 are the same). You wonder to find a more simple formula, I suggest you can provide a sample
or shortcut about your data. Different formulas use different values, if we cannot ensure the values in your data, its hard to help you find the best formula you can use.
So if possible please provide a sample or a shortcut about your excel data, Im glad to help and follow up your reply.
Regards,
George Zhao
TechNet Community Support
May 29th, 2015 5:13am
Other recent topics
