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.

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

I think you could use a VLOOKUP function instead of IF: you have to build up
a search table and than you can use VOLOOKUP.


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

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