Calculate percentage average in Excel
Solved
Bouchon
-
Bouchon -
Bouchon -
Hello,
I’m not sure if the title completely reflects what I'm looking for, but I couldn't think of a better name.
I'm working on an Excel file with (basically) several suppliers, who will provide various services, and all these services have different compliance rates.
To obtain the compliance rate per service, I used an "if" function so that based on the comments, it returns a compliance rate (100%; 75%; 50%, etc).
Now, this is where I'm stuck; I can't find the solution to calculate the overall average of the services by suppliers.
I tried using the average function, but it didn't work, so I'm probably doing it wrong. And let’s just say that me and pivot tables aren’t exactly best friends.
Is there a specific technique when the content of the cells is not a number but the result of a formula?
Thank you for your help!
Best regards
I’m not sure if the title completely reflects what I'm looking for, but I couldn't think of a better name.
I'm working on an Excel file with (basically) several suppliers, who will provide various services, and all these services have different compliance rates.
To obtain the compliance rate per service, I used an "if" function so that based on the comments, it returns a compliance rate (100%; 75%; 50%, etc).
Now, this is where I'm stuck; I can't find the solution to calculate the overall average of the services by suppliers.
I tried using the average function, but it didn't work, so I'm probably doing it wrong. And let’s just say that me and pivot tables aren’t exactly best friends.
Is there a specific technique when the content of the cells is not a number but the result of a formula?
Thank you for your help!
Best regards
1 réponse
Hello
whether the content of a cell is a number or the result of a formula, it shouldn't influence the result, as long as you refer to 100% etc... it should be numerical values
consequently
What is the formula placed in the % cells
Looking forward to hearing from you
best regards
whether the content of a cell is a number or the result of a formula, it shouldn't influence the result, as long as you refer to 100% etc... it should be numerical values
consequently
AVERAGEIF(supplier field; supplier; % field)should work
What is the formula placed in the % cells
Looking forward to hearing from you
best regards
1st column: supplier names (A)
2nd column: compliance = "yes" or "no" (B)
3rd column: detail of non-compliance: either "plan" or "entry" or "plan+entry" (C)
4th column: the detail (D)
5th column: compliance rate with the formula: =IF(C15="PLAN+ENTRY","50%",IF(C15="PLAN","75%",IF(C15="ENTRY","75%",100%)))
6th column: average: not working. With the example supplier xxx: (so I filter to have only this supplier) =AVERAGE(E30:E1823) (E30:E1823) being the set of percentages arising from the services based on the supplier xxx
I hope I am clear..
Thank you for your help
Best regards
try
=IF(C15="PLAN+SAISIE",0.5,IF(C15="PLAN",0.75,IF(C15="SAISIE",0.75,1)))
Hello Vaucluse, sorry for the intrusion!
:o)