Re: Condensing a Range of numbers.

Quote:

Originally Posted by

**jasantacruz** Right so I think I am completely useless at this. I went and started a new work book. It was my first effort and was layed out in a bizarre fashion. So the issue now is that everything has changed. After testing and applying the past stats there where some real issues with the fact they are too low.

So ignoring absolutely everything above, I have been desperately trying to sort a new formula out on my own. But after three days trying to figure this out. I admit defeat.

I am at the stage where I have a number in A1 between 0-98. But I need to condense these. Without the figure ever going above 99.

IF A1 is;

0-20 then condense to 40-49

21-45 condense to 50-64

46-60 condense to 65-75

61-80 condense to 76-85

81-100 condense to 86-99

Any help and I would "again" be very grateful. I can't seem to get to grips with using more than one formula in a Cell.

Also part of the reason I started again was because my workbook was sluggish to scroll down when selected various cells. Am I right in saying this was because I had layed out the cells poorly?

Also is it possible to add an IF formula to the same formula that I want above. For example after the formula gives me a condensed number then I apply an =IF(B1="CB", -10, 0) to the same cell? I don't think I can, but thought it was worth an ask ;)

So have been trying to figure out the formula for this.

Have got this far (Not sure it's even right, the maths need to be spot on or it won't work for me);

=IF(A1>100,99,IF(A1>80, ((99-86)/(100-80))*(A1-80)+86,IF(A1>60, ((85-75)/(81-61))*(A1-61)+76,IF(A1>45,((75-64)/

And then I seem to get lost. I keep looking over and over the formula's that you have given me already Empty Set but at this point (In my eyes anyway) any pattern dissapeard in regard to the next (....-....) ) i'm lost. I mean am I even on the right track.