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?
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):
preserving those proportions (IF that is the requirement) can be achieved with
0
in A2 and: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.