Re: IF statement query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Just stick a - before the IF

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JaB" <JaB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1B48B332-F3DF-42D7-99E1-7AFACDB27C54@xxxxxxxxxxxxxxxx
Thanks Pete.

I realised that I had the criteria back to front. I should have said for
every increment of 30 over 120 the formula should return minus 10, and for
every increment of 30 under 120 the formula should add 10. How does this
alter the formula?

Thanks

"Pete_UK" wrote:

With your number in A1, this formula in B1 should do the trick:

=IF(A1<=0,0,IF(A1<=120,INT((A1-120)/30)*10,(INT((A1-120)/30)+1)*10))

Negative values in A1 are treated the same as zero, and this also
returns a zero if A1 is 120. Copy down column B if you have other
values in column A that you want this to apply to.

Hope this helps.

Pete

JaB wrote:
Hi

Im hoping someone can assist me in creating a formula that does the
following.

I would like to look at cell A1 and if that cell contains a number
greater
than 120, to return a figure of 10 for each increment of 30 over 120
but if
A1 contains a figure less than 120 it should return a figure of minus
10 for
each increment of 30 under 120. If the cell A1 is 0 then it should
return a
0.

For instance;
A1=121 formula returns 10 , or,
A1=119 formula returns -10, or,
A1=211 formula returns 40 etc.

Any ideas?

Thanks




.

Relevant Pages

  • RE: Formulas go away
    ... the issue I see here for using a single formula is the criteria for ... that cell, but with the formula I am using it will continue to add one all ... formula's correct to do what I want the spredsheet to do. ... wanted the cell to either be a blank cell or a zero, ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Adding text or number?
    ... put 100 in an un-used cell ... select the cells you want to increment ... re-select the same cells you just incremented ... the last step restores the leading zero. ...
    (microsoft.public.excel.misc)
  • Re: =IF
    ... otherwise you'll be trying to add a number onto " " when L3517 is zero. ... Having trouble adding criteria to an IF function. ... Using in Cell S3517: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: =IF
    ... otherwise you'll be trying to add a number onto " " when L3517 is zero. ... Having trouble adding criteria to an IF function. ... Using in Cell S3517: ... from cell L3517, with no 1% discount and no rounding, in cell S3517. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: IF statement query
    ... Bob Phillips wrote: ... I realised that I had the criteria back to front. ... Negative values in A1 are treated the same as zero, ... I would like to look at cell A1 and if that cell contains a number ...
    (microsoft.public.excel.worksheet.functions)