IF statement query

J

JaB

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
 
F

Franz Verga

JaB said:
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
Click to expand...

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
 
P

Pete_UK

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
 
J

JaB

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
 
P

Pete_UK

The easiest way is to put a minus after the commas in the second IF
clause, i.e.:

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

Check it out with some test data - this should reverse the values you
had with the previous formula.

Hope this helps.

Pete
 
B

Bob Phillips

Just stick a - before the IF

--
HTH

Bob Phillips

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