| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
| Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions | |||
Linear Mode
