OK, Bob, mine was the second easiest way! <bg

Pete

Bob Phillips wrote:
Just stick a - before the IF

--
HTH

Bob Phillips

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

"JaB" wrote in message
...
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