LinkBack URL
About LinkBacks
Afternoon all,
I have a schedule of KPIs that users can amend the percentage of. I'd like to add a formula at the bottom to double check that the sum of the KPI groupings each adds up to 100%.
Example attached.
Thanks in advance,
Snook
Afternoon all,
I have a schedule of KPIs that users can amend the percentage of. I'd like to add a formula at the bottom to double check that the sum of the KPI groupings each adds up to 100%.
Example attached.
Thanks in advance,
Snook
hi there. how about:
=SUMPRODUCT(--(1/SUMIF(A2:A25,A2:A25,B2:B25)<>1))=0
you could wrap IF around it if you want it to return OK or REVIEW
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
Maybe try this:
=IF(SUM(B2:B25) < SUMPRODUCT(--(FREQUENCY(MATCH(A2:A25,A2:A25,0),ROW(A2:A25)-ROW(A2)+1) > 0))*100,"Review","OK")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks all, benishiryo's solution worked a treat. AliGW's didn't seem to do what I required.
Regards,
Snook
In what way did it not do what was required? It provided the output you asked for.
Hi AliGW,
I've amended the attached so that all of the KPI groups add up to 100% and your solution is outputting 'Review' when I would have expected it to say 'OK'.
Regards,
Snook
That's because the cells are set as percentages. Just change the formula to this:
=IF(SUM(B2:B25) < SUMPRODUCT(--(FREQUENCY(MATCH(A2:A25,A2:A25,0),ROW(A2:A25)-ROW(A2)+1) > 0)),"Review","OK")
Cool, thanks for clarifying.
Snook
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks