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