13 Replies Latest reply: Nov 24, 2012 8:08 PM by Wayne Contello
Garden Frugal Level 1 Level 1 (0 points)

Okay, I need some help.  I can't seem to figure this out and it's making me crazy.

 

I need with a formula that:

takes an input value in a cell and minus a set value.  Let's say ____ - 29.92

After it returns that value, I need to multiply it by another cell in increments of .1

So if it returned .07 it should multiply the cell by zero.  If it was 1.2 it should multiply it by 1, and so on.

 

Please help.  I don't know why I can't figure this out.

 

Thanks.

  • 1. Re: Help with a formula...
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Hi Garden,

     

    What part of that are you having trouble with?

     

    The part I don't understand is your example.

     

    Jerry

  • 2. Re: Help with a formula...
    ProImagesPa Level 1 Level 1 (65 points)

    Try this:

     

    cell a1 --- input a value

    cell a2 --- =a1-29.92

    cell a3 --- =INT(a2)

    cell a4 --- =a2*a3

     

    Is that what you're looking for?

    Greg

  • 3. Re: Help with a formula...
    Wayne Contello Level 6 Level 6 (13,615 points)

    I am also confusd by your example.

     

    You ask that the difference of two values be multiplies by 0.1 but the show that a value of 0.7 is rounded (somehow) to zero.

     

    "increments of" makes me think you want to to divide... like 0.7/0.1 which would be 7  but that is not what you showed.  Or if you meant rounded to the nearest I would have expect 0.07 to round to 0.1.

     

    With clarification this is a tractible problem.

  • 4. Re: Help with a formula...
    ProImagesPa Level 1 Level 1 (65 points)

    "increments of" makes me think you want to to divide..

     

    The word "of " in any word problem means to multiply.

  • 5. Re: Help with a formula...
    Garden Frugal Level 1 Level 1 (0 points)

    Thanks for the help everyone.  Sorry I'm not clear. I think The formula below will work. Thanks Greg.

     

    cell a1 --- input a value

    cell a2 --- =a1-29.92

    cell a3 --- =ROUNDDOWN,a2,1

    cell a4 --- =a3/.1

    cell a5 --- =a4*a6

    cell a6 --- penalty

     

    So for every .1 difference in a2, I need to to apply a penalty. 

    The penalty is only in groups of .1

    so if a2 = .07 it doesn't need to apply a penalty because it's not greater than .1

    if it was 0.12 then it only needs to apply 1 penalty.

    if it was 0.22 then it applies 2 times the penalty

     

    .0 off = penalty * 0

    .1 off = penalty * 1

    .2 off = penalty * 2

    .3 off = penalty * 3 and so on.

     

    I need this to work for a positive number and a negative number.  The penalty changes if the difference is + or -.

     

    I don't know if this makes sense or not.

     

    Thanks for the help.

    Clint

  • 6. Re: Help with a formula...
    Garden Frugal Level 1 Level 1 (0 points)

    So I was thinking this....

     

    cell a1 --- input a value

    cell a2 --- =a1-29.92

    cell a3 --- =ROUNDDOWN,a2,1

    cell a4 --- =a3/.1

    cell a5 --- =a4*a6

     

    if a5 >0 apply penalty A

    if a5 <0 apply penalty B

  • 7. Re: Help with a formula...
    ProImagesPa Level 1 Level 1 (65 points)

    I have no idea where you're headed, but good luck.  I looks like you're on your way.

  • 8. Re: Help with a formula...
    Garden Frugal Level 1 Level 1 (0 points)

    I still don't make sense?

     

    I need it to multiply a cell, if my input data is one tenth off the set value.  For every tenth off I need it to * another cell.

     

    It can't round up.  It has to be a whole tenth off before * that cell.

    example:

    if it's 0.7 it will multiply the cell by 0

    if it's 0.1 it will multiply the cell by 1

    if it's 0.12 it will multiply the cell by 1

    if it's 0.2 it will multiply the cell by 2

    and if it's 0.22 it will multiply the cell by 2

     

    and so on....  I don't know how else to explain it.

     

    Thanks for the help.  I think I got it figured out.  Maybe. 

     

    Clint

  • 9. Re: Help with a formula...
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Clint,

     

    Please post a screen shot of what you have so far.

     

    Jerry

  • 10. Re: Help with a formula...
    Barry Level 7 Level 7 (29,180 points)

    Hi Clint,

     

    I think the clarity problem has to do with an error in proofreading your example. You wrote:

     

    I need it to multiply a cell, if my input data is one tenth off the set value.  For every tenth off I need it to * another cell.

     

    It can't round up.  It has to be a whole tenth off before * that cell.

    example:

    if it's 0.7 it will multiply the cell by 0

    if it's 0.1 it will multiply the cell by 1

    if it's 0.12 it will multiply the cell by 1

    if it's 0.2 it will multiply the cell by 2

    and if it's 0.22 it will multiply the cell by 2

     

    What doesn't make sense is this line:

    if it's 0.7 it will multiply the cell by 0

     

    Should it be:

    if it's 0.07 it will multiply the cell by 0

     

    Regards,

    Barry

  • 11. Re: Help with a formula...
    Garden Frugal Level 1 Level 1 (0 points)

    Jerrold,

     

    Okay I'll work on it right now.

     

    Clint

  • 12. Re: Help with a formula...
    Garden Frugal Level 1 Level 1 (0 points)

    That is very true.  Thanks Barry.  My bad. I was going on little sleep and maybe too much wine. 

     

    Thanks,

    Clint

  • 13. Re: Help with a formula...
    Wayne Contello Level 6 Level 6 (13,615 points)

    Clint,

     

    The other suggestion I can make is rather than describing the solution you want describe the problem you want to solve.  We are good at meeting a specification.  You are trying to describe how to solve the problem rather than provide the description of THE problem.