Creating a Percentage Table/Series

[jamazi]

Hi all,

I need help in making a formula or equation so I can make a series percentage, that could extend up to 100 (only made up to 7 in this example).
I need a formula to distribute 100% depending on the rank, 1 being the highest.

It should always equal to 100. In column 4, an example of what should happen would be:
1 80%
2 20%

and so on..

Any if your inputs will be greatly appreciated.

1	100%	1	%	1	%	1	%	1	%	1	%	1	%
		2	%	2	%	2	%	2	%	2	%	2	%
				3	%	3	%	3	%	3	%	3	%
						4	%	4	%	4	%	4	%
								5	%	5	%	5	%
										6	%	6	%
												7	%

<tbody>
</tbody>

 

[s hal]

This should be easy, but I dont understand why in your example 1 is 80% and 2 is 20%. More insight to this pattern would help anyone to solve your problem... For all I know, the next example is 50%, 30% and 20%, but there's no rhyme or reason to that beyond the fact they add up to 100%, if you follow...

 

[jamazi]

Hi s hal thank you for the response.

It's just an example I had to use. Column 4 only has 2 numbers so I made it 80% and 20%. Your example is fitted to column 6 i believe.
Yes, more insight indeeed will help tremendously since I need to come up to at least up to 110. Do you think it's possible to make a formula for this one? or does it have to be made manuall?

Thanks again.

 

[s hal]

It can easily be a formula or macro, but you need to have some method or pattern to make it work... Your question so far is "is this possible?" The answer is yes. Now you need to refine your question to actually produce something. I have no way to solve this theoretical problem as presented...

 

[jamazi]

I see, thanks s hal.

I hope someone could help me out with this, it would be a huge help.

 

[s hal]

You're going to need to provide something for anyone to know what you want for sure.. The best I can think of is something like the following:

2/3 + 1/3
3/6 + 2/6 + 1/6
4/10 + 3/10 + 2/10 + 1/10
5/15 + 4/15 + 3/15 + 2/15 + 1/15

If this is the pattern you want, then that's something doable, but you started with 80/20 and I'm not sure where you're going from there - if you understand my dilemma now?...

 

[jamazi]

Hi s hal, yes I perfectly understand.. you can just ignore the 80, 20 since they are just examples (my bad on making those numbers up).
This pattern looks promising.

 

[s hal]

So that equation would be: (N*(N+1))/2 for the denominator, and then the values 1-N for the numerator where N is the largest value you have..

SO for instance, 2 would be 1 / 2*(2+1)/2 and 2/ 2*(2+1)/2 (or the 2/3 and 1/3 I listed.

The best way to accomplish this that I see is to have a number starting with 1 and going to 110 at the top of the rows, and then referencing that in place of all the N's and values of 1-110 along the left or far right that you reference for the 1-N numerator.

Hope that helps!

(and just fair warning, the largest value would be on the bottom if you did it that way)

 

[jamazi]

Hi s hal, this actually worked! You're a lifesaver.

Thank you so much! :D

 

[s hal]

Of course it actually worked! So much doubt

It was more of a math question than excel, however...