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