Home |
Search |
Today's Posts |
#1
|
|||
|
|||
If Function
Hello everyone,
I am trying to use if function however Nested If is limited to 7. How do I over come this. Here is the data Date: 01/02/2016 01/02/2016 07/02/2020 07/02/2020 07/02/2015 02/02/2015 12/02/2015 12/02/2015 10/02/2017 01/02/2016 ----------- Maturity Bucket: 0 to 1 month 1-Mar-13 1 to 3 months 1-May-13 3 to 6 months 1-Aug-13 6 to 12 months 1-Feb-14 1 to 2 years 1-Feb-15 2 to 3 years 1-Feb-16 3 to 4 years 1-Feb-17 4 to 5 years 1-Feb-18 5 to 7 years 1-Feb-20 7+ years 2-Feb-20 Here is my formulae: =if($U8<$AB$2,$AA$2,if($U8<$AB$3,$AA$3,if($U8<$AB$ 4,$AA$4,if($U8<$AB$5,$AA$5,if($U8<$AB$6,$AA$6,if($ U8<$AB$7,$AA$7,if($U8<$AB$8,$AA$8,if($U8<$AB$9,$AA $9,If($U8<$AB$10,$AA$10,if($U$8$AB$10-1,$AA$11)))))))))) I am trying to check condition and have use concatenate such as & but it still not working. Here is the formulae for &: =IF($U3<$AB$2,$AA$2,"") &IF($U3<$AB$3,$AA$3,"") &IF($U3<$AB$4,$AA$4,"") &IF($U3<$AB$5,$AA$5,"") &IF($U3<$AB$6,$AA$6,"") &IF($U3<$AB$7,$AA$7,"") &IF($U3<$AB$8,$AA$8,"") &IF($U3<$AB$9,$AA$9,"") &IF($U3<$AB$10,$AA$10,"") &IF($U3($AB$10-1),$AA$11,"") Outcome which I don't want as below 2 to 3 years3 to 4 years4 to 5 years5 to 7 years I just want if the condition falls within the Maturity Bucket, return with only One value where it applicable. ie; if 10/02/2017 should return as 3 - 4 years etc. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Function
I am trying to use if function however Nested If is limited to 7. How
do I over come this. One way is to avoid using IF and instead use something like this: =INDEX($AA$2:$AA$11,MATCH($U3,$AB$2:$AB$11,1)) Hope this helps getting started. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Function Question: Collect Condition in Dialog Box - But How toInsert into Function Equation? | Excel Programming | |||
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 | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |