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