|
nesting if > 7 or using hlookups in a vlookup
I'm in the process of converting a number of financial spreadsheets from
QuattroPro (QP) to Excel. I have a ProjIncome_Calculations table on sheet 3. This table has a different income accural formula for each investment instrument type. Here's an example . . . IF(AND(Type="Accrual Note",Status="Active"),ROUND(IF(PrevCallDate,((((( Face*(PrevCallPrice/100))*AnnYield)/365)*DATEDIF(PrevCallDate,ProjIncome_Date+1,"d"))+ (((PrevCallPrice/100)-1)*Face)),(((Cost*AnnYield)/365)*IF(Price<100,DATEDIF(SttlDate,(ProjIncome_Da te+1),"d"),DaysHeld_CrntYr)))-((Cost-Face)+GLAccr_Dec31),2),"")) These accrual figures roll into a Summary sheet (sheet 1) utilizing the following formula: IF(Type="Accrual Note",VLOOKUP(ID,ProjIncome_Calculations,2),IF(OR( Type="BA",Type="BDN"),VLOOKUP(ID,ProjIncome_Calcul ations,3),IF(Type="Bond",VLOOKUP(ID,ProjIncome_Cal culations,4),IF(OR(Type="Equity Linked Note (A)",Type="STRIP / Coupons",Type="STRIP / Residuals"),VLOOKUP(ID,ProjIncome_Calculations,5), IF(Type="Equity Linked Note (B)",VLOOKUP(ID,ProjIncome_Calculations,6),IF(Type ="Floater (A)",VLOOKUP(ID,ProjIncome_Calculations,7),IF(Type ="Floater (B)",VLOOKUP(ID,ProjIncome_Calculations,8),VLOOKUP (ID,ProjIncome_Calculations,9)))))))) I require 1 more if or to use the =isblank for those lines there the ID field or the Statis field are blank. Ideally I'd like the formula to Hlookup the instrument type in the title of the ProjIncome_Calculations table, then Vlookup the investment ID in column 1 of the table and populate the appropriate accrual. Can you help? PS . . . QP doesn't have the 7 limit on nested ifs, so these formulae have worked beautifully in the past! -- .. . . Cheers C.Pflugrath |
Hi
does your lookup table has a heading row. If yes you may use a INDEX/MATCH combination: =INDEX(A1:G20,MATCH(row_lookup,A1:A20,0),MATCH(col _lookup,A1:F1,0)) -- Regards Frank Kabel Frankfurt, Germany "C.Pflugrath" schrieb im Newsbeitrag ... I'm in the process of converting a number of financial spreadsheets from QuattroPro (QP) to Excel. I have a ProjIncome_Calculations table on sheet 3. This table has a different income accural formula for each investment instrument type. Here's an example . . . IF(AND(Type="Accrual Note",Status="Active"),ROUND(IF(PrevCallDate,((((( Face*(PrevCallPrice/1 00))*AnnYield)/365)*DATEDIF(PrevCallDate,ProjIncome_Date+1,"d"))+ (((Pre vCallPrice/100)-1)*Face)),(((Cost*AnnYield)/365)*IF(Price<100,DATEDIF( SttlDate,(ProjIncome_Date+1),"d"),DaysHeld_CrntYr) ))-((Cost-Face)+GLAcc r_Dec31),2),"")) These accrual figures roll into a Summary sheet (sheet 1) utilizing the following formula: IF(Type="Accrual Note",VLOOKUP(ID,ProjIncome_Calculations,2),IF(OR( Type="BA",Type="BDN") ,VLOOKUP(ID,ProjIncome_Calculations,3),IF(Type="Bo nd",VLOOKUP(ID,ProjIn come_Calculations,4),IF(OR(Type="Equity Linked Note (A)",Type="STRIP / Coupons",Type="STRIP / Residuals"),VLOOKUP(ID,ProjIncome_Calculations,5), IF(Type="Equity Linked Note (B)",VLOOKUP(ID,ProjIncome_Calculations,6),IF(Type ="Floater (A)",VLOOKUP(ID,ProjIncome_Calculations,7),IF(Type ="Floater (B)",VLOOKUP(ID,ProjIncome_Calculations,8),VLOOKUP (ID,ProjIncome_Calcul ations,9)))))))) I require 1 more if or to use the =isblank for those lines there the ID field or the Statis field are blank. Ideally I'd like the formula to Hlookup the instrument type in the title of the ProjIncome_Calculations table, then Vlookup the investment ID in column 1 of the table and populate the appropriate accrual. Can you help? PS . . . QP doesn't have the 7 limit on nested ifs, so these formulae have worked beautifully in the past! -- . . . Cheers C.Pflugrath |
On Fri, 12 Nov 2004 12:02:06 -0800, "C.Pflugrath"
wrote: I'm in the process of converting a number of financial spreadsheets from QuattroPro (QP) to Excel. I have a ProjIncome_Calculations table on sheet 3. This table has a different income accural formula for each investment instrument type. Here's an example . . . IF(AND(Type="Accrual Note",Status="Active"),ROUND(IF(PrevCallDate,(((( (Face*(PrevCallPrice/100))*AnnYield)/365)*DATEDIF(PrevCallDate,ProjIncome_Date+1,"d"))+ (((PrevCallPrice/100)-1)*Face)),(((Cost*AnnYield)/365)*IF(Price<100,DATEDIF(SttlDate,(ProjIncome_Da te+1),"d"),DaysHeld_CrntYr)))-((Cost-Face)+GLAccr_Dec31),2),"")) These accrual figures roll into a Summary sheet (sheet 1) utilizing the following formula: IF(Type="Accrual Note",VLOOKUP(ID,ProjIncome_Calculations,2),IF(OR (Type="BA",Type="BDN"),VLOOKUP(ID,ProjIncome_Calcu lations,3),IF(Type="Bond",VLOOKUP(ID,ProjIncome_Ca lculations,4),IF(OR(Type="Equity Linked Note (A)",Type="STRIP / Coupons",Type="STRIP / Residuals"),VLOOKUP(ID,ProjIncome_Calculations,5) ,IF(Type="Equity Linked Note (B)",VLOOKUP(ID,ProjIncome_Calculations,6),IF(Typ e="Floater (A)",VLOOKUP(ID,ProjIncome_Calculations,7),IF(Typ e="Floater (B)",VLOOKUP(ID,ProjIncome_Calculations,8),VLOOKU P(ID,ProjIncome_Calculations,9)))))))) I require 1 more if or to use the =isblank for those lines there the ID field or the Statis field are blank. Ideally I'd like the formula to Hlookup the instrument type in the title of the ProjIncome_Calculations table, then Vlookup the investment ID in column 1 of the table and populate the appropriate accrual. Can you help? I'd suggest a somewhat different approach. Use a lookup table to determine the appropriate column for summary sheet formula. For example, if I've parsed your formula correctly, you could set up a lookup table name Type_Table with the following data: Accrual Note 2 BA 3 BDN 3 Bond 4 Equity Linked Note (A) 5 STRIP / Coupons 5 STRIP / Residuals 5 Equity Linked Note (B) 6 Floater (A) 7 Floater (B) 8 Then change your summary formula to something like: VLOOKUP(ID,ProjIncome_Calculations,IF(ISNA(VLOOKUP (Type,Type_Table,2,FALSE)),9,VLOOKUP(Type,Type_Tab le,2,FALSE))) Now then, I'm not sure what you want to do if ID or Statis fields are blank, but hopefully this will start you off in the right direction. PS . . . QP doesn't have the 7 limit on nested ifs, so these formulae have worked beautifully in the past! --ron |
Fantastic. So far, working extremely well. Thank you Ron
"Ron Rosenfeld" wrote: On Fri, 12 Nov 2004 12:02:06 -0800, "C.Pflugrath" wrote: I'm in the process of converting a number of financial spreadsheets from QuattroPro (QP) to Excel. I have a ProjIncome_Calculations table on sheet 3. This table has a different income accural formula for each investment instrument type. Here's an example . . . IF(AND(Type="Accrual Note",Status="Active"),ROUND(IF(PrevCallDate,(((( (Face*(PrevCallPrice/100))*AnnYield)/365)*DATEDIF(PrevCallDate,ProjIncome_Date+1,"d"))+ (((PrevCallPrice/100)-1)*Face)),(((Cost*AnnYield)/365)*IF(Price<100,DATEDIF(SttlDate,(ProjIncome_Da te+1),"d"),DaysHeld_CrntYr)))-((Cost-Face)+GLAccr_Dec31),2),"")) These accrual figures roll into a Summary sheet (sheet 1) utilizing the following formula: IF(Type="Accrual Note",VLOOKUP(ID,ProjIncome_Calculations,2),IF(OR (Type="BA",Type="BDN"),VLOOKUP(ID,ProjIncome_Calcu lations,3),IF(Type="Bond",VLOOKUP(ID,ProjIncome_Ca lculations,4),IF(OR(Type="Equity Linked Note (A)",Type="STRIP / Coupons",Type="STRIP / Residuals"),VLOOKUP(ID,ProjIncome_Calculations,5) ,IF(Type="Equity Linked Note (B)",VLOOKUP(ID,ProjIncome_Calculations,6),IF(Typ e="Floater (A)",VLOOKUP(ID,ProjIncome_Calculations,7),IF(Typ e="Floater (B)",VLOOKUP(ID,ProjIncome_Calculations,8),VLOOKU P(ID,ProjIncome_Calculations,9)))))))) I require 1 more if or to use the =isblank for those lines there the ID field or the Statis field are blank. Ideally I'd like the formula to Hlookup the instrument type in the title of the ProjIncome_Calculations table, then Vlookup the investment ID in column 1 of the table and populate the appropriate accrual. Can you help? I'd suggest a somewhat different approach. Use a lookup table to determine the appropriate column for summary sheet formula. For example, if I've parsed your formula correctly, you could set up a lookup table name Type_Table with the following data: Accrual Note 2 BA 3 BDN 3 Bond 4 Equity Linked Note (A) 5 STRIP / Coupons 5 STRIP / Residuals 5 Equity Linked Note (B) 6 Floater (A) 7 Floater (B) 8 Then change your summary formula to something like: VLOOKUP(ID,ProjIncome_Calculations,IF(ISNA(VLOOKUP (Type,Type_Table,2,FALSE)),9,VLOOKUP(Type,Type_Tab le,2,FALSE))) Now then, I'm not sure what you want to do if ID or Statis fields are blank, but hopefully this will start you off in the right direction. PS . . . QP doesn't have the 7 limit on nested ifs, so these formulae have worked beautifully in the past! --ron |
On Fri, 12 Nov 2004 14:51:04 -0800, "C.Pflugrath"
wrote: Fantastic. So far, working extremely well. Thank you Ron You're welcome. Thank you for the feedback. --ron |
| All times are GMT +1. The time now is 05:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com