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

Question

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?

Are you asking us what a formula already in place is? The only thing i can see is where there are questino marks you could set them to, `0.9`,`0.89`,`0.7`,`0.69`,`0.5`,`0.49` but i really have no idea what you are asking — user2140261, Jan 20 '14 at 19:57
The closest I can get is that this is a non linear inverse relationship. The data you have provided is insufficient to arrive at any conclusion. The New Scale values can be calculated as in the above comment but that is all I can think of. — ucsunil, Jan 20 '14 at 20:05
the problem is there is no formula for cells in old_scale. i was given these values for old_scale. So i need to understand how the min & max for each cutoff% calculated. Yes, i need a formula to generate this scale. The scale starts from -5 to +5. my thought is that once i understand how these values were calculated, then i can apply that same formula to generate any scale (e.g. from 0 to 1) for the same cutoffs (calculating their min & max). — sifar, Jan 21 '14 at 03:08

score 0 · Answer 1 · answered May 13 '14 at 20:32

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.

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

1 Answers1