13 Replies Latest reply: Nov 24, 2012 8:08 PM by Wayne Contello
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.

Thanks.

• ###### 1. Re: Help with a formula...
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...
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...
Level 6 (13,620 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...
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...
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...
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...
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...
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...
Level 7 (28,995 points)

Clint,

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

Jerry

• ###### 10. Re: Help with a formula...
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...
Level 1 (0 points)

Jerrold,

Okay I'll work on it right now.

Clint

• ###### 12. Re: Help with a formula...
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...
Level 6 (13,620 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.