Reverse proportional distribution

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Items	Reviews	Ranking	New Rank
A	161	499
B	6774	500
C	5744	504
D	9484	498
E	30	500

The item ranking is not accurate and is currently divided almost evenly among the items. but the total ranking is accurate for all items together. so we would like to redistribute the total ranking in accordance to the review count, and being that a lower ranking number is better than a higher ranking number, a high number of reviews will reflect a lower ranking.

We asked you to upload a workbook. Please read post #2 and follow the instructions. Include expected results. Thanks.

There are no expected results. You need to add these. We need to see what you need the formula to calculate.

Also, how are you calculating the rank? What is the formula that you have used for that?

Here's a wild guess in the absence of any expected results. In D2 copied down:

=(SUM($C$2:$C$6)/SUM($B$2:$B$6))*B2

Is this anywhere close to what you are looking for?

Explain what the opposite of what my formula produces would be - what are you expecting? "I am not sure" really isn't going to help.

In the absence of any clarity on your part, I am not sure how we can help you.

This would give the items with higher reviews a higher ranking

Sorry - I have no clue what you want. It's much too vague (for me).

Well, that's what I am looking for the formula to do.

When you come here and ask for a formula, you are asking us to automate a claculation that YOU know how to make manually. It seems that it's the maths that you are struggling with rather than anything else.

In answer to your question above, are more points good or bad? If the latter, what's the maximum number of negative points that a pupil could get?

Then it can't be done as far as I can tell. Without a maximum number of points you can't work out a proportional ranking.

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. As you are new here, I shall do it for you this ONCE: https://www.mrexcel.com/board/thread...ution.1241410/)

LoL... a formula to calculate something you don't know how to calculate manually...

A guess...

=($C$7*$B$7/B2)/SUM($B$7/B$2:B$6)

see file.

the higher the number of reviews, the lower the ranking

Your question is too vague, because there are infinitely many possible distributions that meet your question.

One of the possible distribution that meet your question is:

Please try in D2 and copy down:

Formula:

Please Login or Register  to view this content.

You can change Glenn's formula to this:

=(SUM($B$2:$B$6)*SUM($C$2:$C$6)/B2)/SUM(SUM($B$2:$B$6)/B$2:B$6)

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Thank you! The only issue still left is the B$2:B$6 at the end. I will be using SUMIFS to look up and sum up these totals. and it seems that I won't be able to do that with SUM($B$2:$B$6)/"""B$2:B$6"""). I'm fairly new to Excel and the method of how dividing by B$2:B$6 is used here is unclear to me. is it a spill range?

Provide sample data that requires a SUMIFS function and we can check for you.

Thanks for the rep.

You PM'd me... here's the reply. As I thought, there WAS a lot of redundancy in that. Occam's Razor proves its value once again.

Revised formula:

=SUM($C$2:$C$6)/B2/SUM(1/$B$2:$B$6)

See file for explanation.

You PM'd me, stating that you'd prefer a distribution like:

589
34
36
27
1815

in your words "because it moves the weight slightly away from the higher reviews".

Now you begin to stray from mathematics into... "alternative truth", cookery, art... You need to provide RULES, as Excel cannot understand the niceties of "slightly away".

Of course you could simple whack 100 points off the total of 2501... distribute the 100 evenly amongst all 5 (20 each) and the balance (2401) on the basis of a strict mathematical relationship. But why 100 and not 50, or 200... or any other number that occurs to you?

Can you JUSTIFY the logical basis of the choice of any such adjustment (to yourself/your boss... not to me)??

OK... Can I ruminate on this one?? (it's beer o'clock in Ireland)

You can take a look at this. Vary the value in B8 from 0 (original solution... to 1 - equal ranking). It works as suggested in Post 27. The % of 2501 set in B8 is divided by 5 and allocated to each of A-E. The remainder is allocated in proportion to the reciprocal of the values.

Have a play with it and see if you can decide on a value that gives you an end result that comes close to what you need.

1. Delete all expected results.

2. Use:
=LET(Z,B2:C20,Y,FILTER(Z,INDEX(Z,,1)<>""),B,INDEX(Y,,1),C,INDEX(Y,,2),G1*SUM(C)/ROWS(B)+((1-G1)*SUM(C)/B/SUM(1/B)))

no copy paste. The formula spills results down.

3. Adjust (only) the bit in red to whatever suits - that is sensible (not 1,000,000, but which is future-proof).