Formula to calculate MIN & MAX for each Cutoff between 2 limiting values

2.2k Views Asked by At

i want to calculate MIN & MAX values for different Cutoffs, given their Cutoff %. The Cutoffs itself lie between 2 limiting values.

e.g. there are min & max values calculated for each Cuttoff in Old_Scale where their limiting values are -5 to +5. Also, I would like to know how these were calculated for each cuttoff using the Cutoff% & the limiting values of -5 to +5 i.e. i would like to derive their Formula.

new_max:     1      old_max: 5
new_min:     0      old_min: -5

cutoff% | Cutoff Name    | Old Scale |      New Scale
----------------------------------------------------
10%           Very High    max: 5       max: 1
                           min: 4       min: ?
20%           High         max: 3.99    max: ?
                           min: 2       min: ?
20%           Medium       max: 1.99    max: ?
                           min: 0       min: ?
50%           Low          max: -0.01   max: ?
                           min: -5      min: 0

any ideas on how to calculate?

1

There are 1 best solutions below

0
On

Not sure what you are asking but making a guess.

You have identified that the Cutoffs are 10%, 20%, 20% and 50% (in B1:E1 below):

SO21242742 example

preserving those proportions (IF that is the requirement) can be achieved with 0 in A2 and:

=A2+B1  

in B2 and copied across to E2.

The bands are then delimited in pairs as shown by blue colour, solid outline, green colour and dashed outline.