IF Function
 

Hi
i have a credit control spreadsheet containing credit limit columns from
which i am working out credit terms. i have inserted a column into which i
was entering an IF funcition:
=IF(D8=0,0,IF(D8<=5,5,IF(D8<=10,10,IF(D8<=20,20,IF (D8<=50,50,IF(D8<=100,100,IF(D8<=150,150,IF(D8<=25 0,250,IF(D8<=500,500,IF(D8<=750,750,IF(D8<=1000000 ,1000000)))))))))))
However excel will not except the function arguments from 250 onwards, can
anyone tell me why? or is there an easier way of doing this?!
--
Lois

IF Function
 

Only upto 7 IF conditions is allowed/ Try the below...

=IF(D8=0,0,LOOKUP(D8-1,{0,5,10,20,50,100,150,250,500,750,1000000},{5,10 ,20,50,100,150,250,500,750,1000000}))
--
If this post helps click Yes
---------------
Jacob Skaria


"Lois" wrote:

Hi
i have a credit control spreadsheet containing credit limit columns from
which i am working out credit terms. i have inserted a column into which i
was entering an IF funcition:
=IF(D8=0,0,IF(D8<=5,5,IF(D8<=10,10,IF(D8<=20,20,IF (D8<=50,50,IF(D8<=100,100,IF(D8<=150,150,IF(D8<=25 0,250,IF(D8<=500,500,IF(D8<=750,750,IF(D8<=1000000 ,1000000)))))))))))
However excel will not except the function arguments from 250 onwards, can
anyone tell me why? or is there an easier way of doing this?!
--
Lois

IF Function
 

Easiest way would be to split the formula across 2 cells I think;

Have
=IF(D8=0,0,IF(D8<=5,5,IF(D8<=10,10,IF(D8<=20,20,IF (D8<=50,50,IF(D8<=100,100,IF(D8<=150,150,IF(D8<=25 0,250,A1))))))))
In one cell, and
=IF(D8<=500,500,IF(D8<=750,750,IF(D8<=1000000,1000 000)))
In A1 or where ever you want.

"Lois" wrote in message
...
Hi
i have a credit control spreadsheet containing credit limit columns from
which i am working out credit terms. i have inserted a column into which
i
was entering an IF funcition:
=IF(D8=0,0,IF(D8<=5,5,IF(D8<=10,10,IF(D8<=20,20,IF (D8<=50,50,IF(D8<=100,100,IF(D8<=150,150,IF(D8<=25 0,250,IF(D8<=500,500,IF(D8<=750,750,IF(D8<=1000000 ,1000000)))))))))))
However excel will not except the function arguments from 250 onwards, can
anyone tell me why? or is there an easier way of doing this?!
--
Lois

IF Function
 

yes, this works, Thank-You!
--
Lois


"Jacob Skaria" wrote:

Only upto 7 IF conditions is allowed/ Try the below...

=IF(D8=0,0,LOOKUP(D8-1,{0,5,10,20,50,100,150,250,500,750,1000000},{5,10 ,20,50,100,150,250,500,750,1000000}))
--
If this post helps click Yes
---------------
Jacob Skaria


"Lois" wrote:

Hi
i have a credit control spreadsheet containing credit limit columns from
which i am working out credit terms. i have inserted a column into which i
was entering an IF funcition:
=IF(D8=0,0,IF(D8<=5,5,IF(D8<=10,10,IF(D8<=20,20,IF (D8<=50,50,IF(D8<=100,100,IF(D8<=150,150,IF(D8<=25 0,250,IF(D8<=500,500,IF(D8<=750,750,IF(D8<=1000000 ,1000000)))))))))))
However excel will not except the function arguments from 250 onwards, can
anyone tell me why? or is there an easier way of doing this?!
--
Lois

IF Function
 

Cheers.. 2007 version allows more than 7..

If this post helps click Yes
---------------
Jacob Skaria


"Lois" wrote:

yes, this works, Thank-You!
--
Lois


"Jacob Skaria" wrote:

Only upto 7 IF conditions is allowed/ Try the below...

=IF(D8=0,0,LOOKUP(D8-1,{0,5,10,20,50,100,150,250,500,750,1000000},{5,10 ,20,50,100,150,250,500,750,1000000}))
--
If this post helps click Yes
---------------
Jacob Skaria


"Lois" wrote:

Hi
i have a credit control spreadsheet containing credit limit columns from
which i am working out credit terms. i have inserted a column into which i
was entering an IF funcition:
=IF(D8=0,0,IF(D8<=5,5,IF(D8<=10,10,IF(D8<=20,20,IF (D8<=50,50,IF(D8<=100,100,IF(D8<=150,150,IF(D8<=25 0,250,IF(D8<=500,500,IF(D8<=750,750,IF(D8<=1000000 ,1000000)))))))))))
However excel will not except the function arguments from 250 onwards, can
anyone tell me why? or is there an easier way of doing this?!
--
Lois