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

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

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

Too many late-nights <ebg

Bob

"Pete_UK" wrote in message
ups.com...
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