IF functions

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I am trying to use the "IF" function, but it gives me
errors. I am trying the following:
=if(x2<16,$ad$2,if(x2<18,$ad$3,if(x2<20,$ad$4 etc..

it gives me an error when I reach 24 (I want to go to 44)

Any suggestions?

Thanks in advance!
Mike
 
Mike,

There is a limit of 7 on Nested IFs. Try this instead

=INDEX(AD2:AD45,(X2-14)/2,1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
One way:

=INDEX($AD:$AD,MAX(2,(X2-10)/2))

You don't say what should happen when X2>44 - you may want to add
another term.
 
Small correction to conform with OP's comparisons:

=INDEX(AD2:AD45,(X2-12)/2,1)

also, an X2 value of 44 should reference AD17, so may want to reduce
the range (hard to tell - the OP didn't say what should happen if
X2>44).
 
Also note that even with the correction, if X2 < 12, the function
returns the #VALUE! error.
 
Thanks JE!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I appreciate everyones interest in this problem, and it
is my fault for not explaining clearly enough :)

If I understand everyones replies, it is assumed that the
results are linear. As X2 increases the results are not
linear (prices). I hope I have explained myself better,
and hope more suggestion to solve my problem will follow.

Thanks again!

Mike
..
 
Then use a vlookup table where you have the prices in col ac and the value
desired in col ad
=vlookup(x2,$ac$2:$ad$100,2,0)
 
I am trying to use the "IF" function, but it gives me
errors. I am trying the following:
=if(x2<16,$ad$2,if(x2<18,$ad$3,if(x2<20,$ad$4 etc..

it gives me an error when I reach 24 (I want to go to 44)

Any suggestions?

Thanks in advance!
Mike
Click to expand...

If you are going up by twos, it should give you an error at 32, since Excel's
specifications indicate one can only nest seven functions, and 32 would be your
eight function.

There are several solutions.

You could use VLOOKUP and set up a table with 16-44 in AC2:AC16.

Your formula would then be something like:

=VLOOKUP(X2,AC2:AD16,2)

and you'll need some logic to handle out of range data.


--ron
 
Back
Top