Thread: Condensing a Range of numbers.

Hi everyone, I really hope i have posted this is the right section. Maths is not my greatest strength

But i find my self in a position needing help, and i have been thinking about this for who knows how long and i can't seem to come up with a formula to solve it.

I am creating a conversion tool for gaming reasons.

At the minute i have a number of mathematical equations that gives me a number in the range of 0-100. My problem now is; i need a way of condensing that number into a range of 50-100....

This is where it becomes confusing for me So i apoligise if it makes no sense, but hopefully it does lol

But a priority is needed, (50 is bad, 75 is good, 80 is great, 90 best, 100 perfect) so if the number in the original range is 49 i can't just condess it to be 75/100, because that would mean it is a good stat. When it is average.... So when condenssing the range of numbers a priority is needed, 0 the worst, 100 the best. So 50-55 would be anything from 0-30 from the original list, 56-60 being 31-49 from the original, then 61-65 / 50-59. 66-70 / 60-70..... Then anything above that i would like to be coverted as it appears in the original....

If it helps i am using Excel to create my converter....

Once again i hope i can get some help and i can only apoligise if this is a completely "stupid" question.

Thank You

ja_santacruz

Well if you assume the function is piecewise linear you can make a nested if statement in Excel that should do what you want.
Note I just threw this together you are free to use it, but test it and make sure it is giving the values you wish.

Here is the function.

Code:

=IF(A1>70,A1,IF(A1>60,(1/2)*(A1-60)+65, IF(A1>50,(1/2)*(A1-50)+60,IF(A1>30,(1/4)*(A1-30)+55,6*A1))))

This assumes that the data is is the cell A1.

I hope this helps.

Originally Posted by TheEmptySet

Well if you assume the function is piecewise linear you can make a nested if statement in Excel that should do what you want.
Note I just threw this together you are free to use it, but test it and make sure it is giving the values you wish.

Here is the function.

Code:

=IF(A1>70,A1,IF(A1>60,(1/2)*(A1-60)+65, IF(A1>50,(1/2)*(A1-50)+60,IF(A1>30,(1/4)*(A1-30)+55,6*A1))))

This assumes that the data is is the cell A1.

I hope this helps.

Thank you very much, is almost perfect, I was just getting into IF formulas, and was looking at using individual formulas in each cell, then adding them together, but this formulas saves me a lot of time lol.

Here is what i have used

=IF(B35>70,B35,IF(B35>60,(1/2)*(B35-60)+65, IF(B35>50,(1/2)*(B35-50)+60,IF(B35>30,(1/4)*(B35-30)+55,6*B35))))

I have looked to try and resolve the issue I have but I can't seem to do it, when converting the original values the numbers between 31-100 convert perfectly, I mean perfect it comes out at exactly what i wanted to, according to all my notes but when the original value is 30 or below i get extremely high numbers, with the number 30 it converts to 180.

Yeah that is my bad. The 6*B35 at the end should be (1/6)*B35 I put the reciprocal of the slope in. That should fix it

Originally Posted by TheEmptySet

Yeah that is my bad. The 6*B35 at the end should be (1/6)*B35 I put the reciprocal of the slope in. That should fix it

I really am being a pest now

=IF(B35>70,B35,IF(B35>60,(1/2)*(B35-60)+65, IF(B35>50,(1/2)*(B35-50)+60,IF(B35>30,(1/4)*(B35-30)+55,(1/6)*B35))))

30 now become 5, anything below just gets lower. So its gone to the opposite end of the scale.

Originally Posted by jasantacruz

I really am being a pest now

=IF(B35>70,B35,IF(B35>60,(1/2)*(B35-60)+65, IF(B35>50,(1/2)*(B35-50)+60,IF(B35>30,(1/4)*(B35-30)+55,(1/6)*B35))))

30 now become 5, anything below just gets lower. So its gone to the opposite end of the scale.

Sorry this time I didn't put in the y intercept you just need to add fifty to each value

(1/6)*B35+50

Originally Posted by TheEmptySet

Sorry this time I didn't put in the y intercept you just need to add fifty to each value

(1/6)*B35+50

Fantastic, Working perfectly... think i actually understand what going on with this formula aswell lol

Thank you very much for your help

=IF(B35>70,B35,IF(B35>60,(1/2)*(B35-60)+65, IF(B35>50,(1/2)*(B35-50)+60,IF(B35>30,(1/4)*(B35-30)+55,(1/6)*B35+50))))

Hi again, am really sorry to bother you all again. But I could really do with some help with this one.

Have come across another issue. Have been reading through this formula and can't seem to understand what I need to change.

So this formula condeses numbers from 0-30 to "50-55", 31-49 to "56-60", 50-59 to "61-65", 60-70 to "66-70. Anything above just translates to the same number.

I want to change this to the following;

0-15 to 50-59
16-40 to 60-65
41-56 to 66-70
57-69 to 71-75
70-79 to 76-79
80+ to be same value

Now I think that would mean there has to be one more formula in there.

Thank You In advance.

jasantacruz

Try this as before make sure to check it

=IF(A1 > 80,A1,IF(A1>70,((79-75)/(80-70))*(A1-70)+76,IF(A1>56,((75-70)/(70-57))*(A1-57)+71,IF(A1>40,((71-65)/(56-40))*(A1-41)+66,IF(A1>15,(1/5)*(A1-15)+60,(2/3)*A1+50)))))

Thank you very much, again

Works perfectly, exactly what I looking for. I now have two sets of stats to work with, which is brilliant.

You help was and is very much appreciated.

I can't wait till this converter is done. I thought it was perfect but...

=IF(B39>70,B39,IF(B39>60,(1/2)*(B39-60)+65, IF(B39>50,(1/2)*(B39-50)+60,IF(B39>30,(1/4)*(B39-30)+55,(1/6)*B39+50))))

I want a limit on the highest number. It gets to 100 and over... Whereas my limit is 99. How can I change this formula so that it limits the highest number to 99?

I think this will do what you want.

=IF(A1>100,99, IF(A1 >80, ((99-80)/(100-80))*(A1-80)+80,IF(A1>70,((79-75)/(80-70))*(A1-70)+76,IF(A1>56,((75-70)/(70-57))*(A1-57)+71,IF(A1>40,((71-65)/(56-40))*(A1-41)+66,IF(A1>15,(1/5)*(A1-15)+60,(2/3)*A1+50))))))

If not just notice this (99-80)/(100-80))*(A1-80)+80 is just



Where you start at and end at

Just realised I gave the wrong formula in the first place, the correct one is;

=IF(B39 > 80,B39,IF(B39>70,((79-75)/(80-70))*(B39-70)+76,IF(B39>56,((75-70)/(70-57))*(B39-57)+71,IF(B39>40,((71-65)/(56-40))*(B39-41)+66,IF(B39>15,(1/5)*(B39-15)+60,(2/3)*B39+50)))))

Sorry for the confusion.

Ignore that.

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