Search code examples
formulalibreoffice-calc

Assign integral value to list of relative values

I have an assortment of syrups, each of which has a value - the amount of sugar per volume. As people blend these syrups, I track which ones are used, and created a table to get a Relative Weight of each blend. I understand > Data > Sort > Options > Custom Sort Order.

However, I really don't wish to sort each table, and am looking for a way to parse a column of this list as entered, and return a column with results in an Integral Relative Value of each row, as compared to the weights of syrups in the other rows of the table.

Unique Name   weight Not Unique  Relative Value
blueberry       .250                       2
raspberry       .333                       3
orange          .425                       4
tangerine       .333                       3
blackberry      .225                       1

I am attempting to find the "Relative Sort", a nested function which can assign an integral value of the Unique Name which compares the weights of the syrups. A "Lookup" only works if there is an absolute equality, right?

What if someone doesn't use "blackberry syrup", then "blueberry" is the lightest, and should be labeled as 1.

Is this too complicated for LibreOffice Calc?

It's a recursive greater than/less than/equal to comparison?

Solution

  • IF the problem is calculating the right hand column below from entries that may be sorted ascending by value as on the left:

    enter image description here

    then an answer is, in C2 and copied down to suit (provided C1 is blank or 0):

    =IF(B1<>B2,C1+1,C1)

    Without sorting the RANK function might be simpler and adequate (though in the example returning 5 rather than 4).