Hi ...
How do I get the spread of the percentage in cells AH10:AJ10 to never get below 10% and still add up to 100%?
See attachment (if it came trough)
In advance thanks!
Hi ...
How do I get the spread of the percentage in cells AH10:AJ10 to never get below 10% and still add up to 100%?
See attachment (if it came trough)
In advance thanks!
Could you provide a little context, explain what you're calculating?
Entia non sunt multiplicanda sine necessitate
Sorry ...
Each player are setting an "x" in their cell a, b or c.
The percentage is representing the percentage spread of these x's, i.e. weighing the totalt voting in a, b or c.
I have problem getting my very long formula in the cells AH10:AJ10 to act correctly, at all times, as you can see in my example file.
Here is a link to an updated version at my Onedrive:
https://1drv.ms/x/s!AoTBup2FdjrGg55HI86S8rGoSIs3lg
In AH10 and copy across,
=70% * AD10 / SUM($AD10:$AF10) + 10%
B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ 10 x x x x x x x x x x x x x 6 7 0 42% 48% 10%
Ahh, I see something I did forget.
All values must be rounded up or down to the nearest ten.
And you formula ends in a 0 in all three cells.
Why do you start you formula with 70%?
Have you tested it in other combinations by entering x's in each players cells?
How does that reconcile with "How can I get the percentage to be exact?"
Not if you format them as Percent.And you formula ends in a 0 in all three cells.
Have you tested it in other combinations by entering x's in each players cells?
AD AE AF AG AH AI AJ 16 1 5 1 20% 60% 20% 17 7 4 7 37% 26% 37% 18 0 6 2 10% 63% 28% 19 7 0 3 59% 10% 31% 20 5 5 6 32% 32% 36% 21 4 5 2 35% 42% 23% 22 4 4 3 35% 35% 29% 23 6 3 3 45% 28% 28% 24 3 6 6 24% 38% 38% 25 4 1 6 35% 16% 48%
Thank you very much, SHG
Your solution holds water.
Adding ROUND() did the trick.
=ROUND(70%*AD10/SUM($AD$10:$AF$10)+10%;1)
Work fine now.
My own looooong formula stinks ...
Again; Thanks
What result does that give for {5,5,0}?
What should it give?
Hi ...
It's really gone nearly six years?!?
Well, today I opened the afore mentioned spreadsheet again, and the {5,5,0} spread, gives excatly that, 50% 50% 0%, while I want the result to be 50% 40% 10%. The formula =ROUND(70%*AD10/SUM($AD$10:$AF$10)+10%;1) seems not cope with that combiantion of persentages.
Any idea?
Sorry 'bout the long overddue reply.
This proposal employs four helper columns which may be moved and/or hidden for aesthetic purposes.
1. Cell AL10 is populated using: =MATCH(MODE.SNGL(AD10:AF10),AD10:AF10,0)
2. Cells AM10:AO10 are populated using: =IFERROR(IF($AL10=COLUMNS($AM10:AM10),SUM(AD10,1),AD10),AD10)
3. Cells AD10:AF10 are populated using: =IF($A10="","",IF(COUNTA(B10,F10,J10,N10,R10,V10,Z10)=0,1,COUNTA(B10,F10,J10,N10,R10,V10,Z10)))
4. Cells AH10:AJ10 are populated using: =ROUND(AM10/SUM($AM10:$AO10),1)
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi ...
This seems like a good solution, but when changing AF14 to a zero, the result in cell AJ14 is 0%, even though it should never be less than 10%.
Attachment 782558
Tresfjording
Last edited by Tresfjording; 06-01-2022 at 04:25 PM.
Try modifying the formula in AM10:AO10 so that it reads:Formula:Please Login or Register to view this content.
By the way, selecting attachment 782558 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"
In the future please utilize the information in the banner at the top of the page to attach and Excel file.
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks