## Help with a formula...

364 Views 13 Replies Latest reply: Nov 24, 2012 8:08 PM by Wayne Contello
Calculating status...
Currently Being Moderated
Nov 24, 2012 12:07 AM

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.

• Level 7 (27,430 points)
Currently Being Moderated
Nov 24, 2012 12:58 AM (in response to Garden Frugal)

Hi Garden,

What part of that are you having trouble with?

The part I don't understand is your example.

Jerry

• Level 1 (65 points)
Currently Being Moderated
Nov 24, 2012 7:53 AM (in response to Garden Frugal)

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

• Level 6 (11,965 points)
Currently Being Moderated
Nov 24, 2012 8:22 AM (in response to Garden Frugal)

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.

• Level 1 (65 points)
Currently Being Moderated
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.

• Level 1 (65 points)
Currently Being Moderated
Nov 24, 2012 9:50 AM (in response to Garden Frugal)

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

• Level 7 (27,430 points)
Currently Being Moderated
Nov 24, 2012 12:31 PM (in response to Garden Frugal)

Clint,

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

Jerry

• Level 7 (28,825 points)
Currently Being Moderated
Nov 24, 2012 2:02 PM (in response to Garden Frugal)

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

• Level 6 (11,965 points)
Currently Being Moderated
Nov 24, 2012 8:08 PM (in response to Garden Frugal)

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.

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.