Hello,
I have 2 columns of numbers. One is a system proposal, the other one is what we decide to apply.
I would need a formula which calculate a % of compliance : how many times did we follow the system ?
Constraint 1 : number of lines will not be the same depending on the application.
Constraint 2 : due to constraint 1, I don't want the % to be decreased or increase by blank cells (when there is no data inside).
Thanks for your help !
Last edited by Sakura92; 11-13-2013 at 08:25 AM.
Hello all,
As I was advised to do it, I attached an example.
In red, the cell where I would like to have the forumla.
Thanks for help
Please try this function
=SUMPRODUCT(1*($E$10:$E$29=$F$10:$F$29))/COUNTA($F$10:$F$29)
Please click 'Add reputation', if my answer helped you.
Hello ramananhrm,
Thanks for your answer. This working but not perfect.
As I said, numbers of lines in the report are not the same each time. So this time I got 20 customers but I would get only 18 or 22 next time.
With the assummption that I have the same numbers for all, If I remove data on the 2 last customers, the % become 111%.
How can it ignore lines where there is no data ?
maybe:
=SUMPRODUCT(--($E$10:$E$40=$F$10:$F$40),--(F10:F40<>""))/COUNT($F$10:$F$40)
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Hopefully this should work.
=SUMPRODUCT(1*($E$10:$E$29=$F$10:$F$29))/SUMPRODUCT(--($E$10:$E$29<>"")*--($F$10:$F$29<>""))
Formula from benishiryo is working perfectly thanks !
ramananhrm, sorry but the formula still consider blanks as i got more than 100%
Anyway, thanks both for your help !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks