Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Math Help - Condensing a Range of numbers.

  1. #1
    Newbie
    Joined
    May 2011
    Posts
    11

    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
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Behold, the power of SARDINES!
    TheEmptySet's Avatar
    Joined
    Feb 2008
    From
    Yuma, AZ, USA
    Posts
    3,764
    Thanks
    78
    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.
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    May 2011
    Posts
    11
    Quote Originally Posted by TheEmptySet View Post
    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.
    Follow Math Help Forum on Facebook and Google+

  4. #4
    Behold, the power of SARDINES!
    TheEmptySet's Avatar
    Joined
    Feb 2008
    From
    Yuma, AZ, USA
    Posts
    3,764
    Thanks
    78
    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
    Follow Math Help Forum on Facebook and Google+

  5. #5
    Newbie
    Joined
    May 2011
    Posts
    11
    Quote Originally Posted by TheEmptySet View Post
    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.
    Follow Math Help Forum on Facebook and Google+

  6. #6
    Behold, the power of SARDINES!
    TheEmptySet's Avatar
    Joined
    Feb 2008
    From
    Yuma, AZ, USA
    Posts
    3,764
    Thanks
    78
    Quote Originally Posted by jasantacruz View Post
    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
    Follow Math Help Forum on Facebook and Google+

  7. #7
    Newbie
    Joined
    May 2011
    Posts
    11
    Quote Originally Posted by TheEmptySet View Post
    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
    Follow Math Help Forum on Facebook and Google+

  8. #8
    Newbie
    Joined
    May 2011
    Posts
    11
    =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
    Follow Math Help Forum on Facebook and Google+

  9. #9
    Behold, the power of SARDINES!
    TheEmptySet's Avatar
    Joined
    Feb 2008
    From
    Yuma, AZ, USA
    Posts
    3,764
    Thanks
    78
    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)))))
    Follow Math Help Forum on Facebook and Google+

  10. #10
    Newbie
    Joined
    May 2011
    Posts
    11
    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.
    Follow Math Help Forum on Facebook and Google+

  11. #11
    Newbie
    Joined
    May 2011
    Posts
    11
    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?
    Follow Math Help Forum on Facebook and Google+

  12. #12
    Behold, the power of SARDINES!
    TheEmptySet's Avatar
    Joined
    Feb 2008
    From
    Yuma, AZ, USA
    Posts
    3,764
    Thanks
    78
    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

    y=\left(\frac{y_2-y_1}{x_2-x_1} \right)(x-x_1)+y_1

    Where you start at (x_1,y_1) and end at (x_2,y_2)
    Follow Math Help Forum on Facebook and Google+

  13. #13
    Newbie
    Joined
    May 2011
    Posts
    11
    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.
    Follow Math Help Forum on Facebook and Google+

  14. #14
    Newbie
    Joined
    May 2011
    Posts
    11
    Ignore that.
    Follow Math Help Forum on Facebook and Google+

  15. #15
    Newbie
    Joined
    May 2011
    Posts
    11
    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
    Last edited by jasantacruz; June 13th 2011 at 03:46 PM.
    Follow Math Help Forum on Facebook and Google+

Page 1 of 2 12 LastLast

Similar Math Help Forum Discussions

  1. Changing the range of numbers...
    Posted in the Math Software Forum
    Replies: 4
    Last Post: July 6th 2011, 08:45 PM
  2. Condensing Logs :(
    Posted in the Algebra Forum
    Replies: 2
    Last Post: April 7th 2011, 10:25 PM
  3. Condensing to One Radical
    Posted in the Algebra Forum
    Replies: 6
    Last Post: May 18th 2010, 01:32 AM
  4. Replies: 2
    Last Post: April 16th 2010, 04:28 AM
  5. Condensing Logarithms
    Posted in the Pre-Calculus Forum
    Replies: 1
    Last Post: September 29th 2009, 11:51 AM

Search Tags


/mathhelpforum @mathhelpforum