
1. Re: Help with a formula...
Jerrold Green1 Nov 24, 2012 12:58 AM in response to Garden FrugalHi 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 Nov 24, 2012 7:53 AM in response to Garden FrugalTry this:
cell a1  input a value
cell a2  =a129.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 Nov 24, 2012 8:22 AM in response to Garden FrugalI 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 Nov 24, 2012 8:33 AM in response to Wayne Contello"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 Nov 24, 2012 9:27 AM in response to Garden FrugalThanks 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  =a129.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 Nov 24, 2012 9:46 AM in response to Garden FrugalSo I was thinking this....
cell a1  input a value
cell a2  =a129.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 Nov 24, 2012 9:50 AM in response to Garden FrugalI 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 Nov 24, 2012 10:55 AM in response to ProImagesPaI 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 Nov 24, 2012 12:31 PM in response to Garden FrugalClint,
Please post a screen shot of what you have so far.
Jerry

10. Re: Help with a formula...
Barry Nov 24, 2012 2:02 PM in response to Garden FrugalHi 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 Nov 24, 2012 7:49 PM in response to Jerrold Green1Jerrold,
Okay I'll work on it right now.
Clint

12. Re: Help with a formula...
Garden Frugal Nov 24, 2012 7:49 PM in response to BarryThat 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 Nov 24, 2012 8:08 PM in response to Garden FrugalClint,
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.