Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
VictoriaG
 
Posts: n/a
Default if logical test true, then hlookup, if false then difference betwe

MSExcel2002 SP3
This is a confusing scenario, I'll do my best to explain.

A B C D
4 May-05 Jun-05 Jul-05

6 1-month 2.913% 3.298% false

B6 =IF(B4<'Qtr Lookup Box'!$A:$A,HLOOKUP(B4,'bloomberg swap
curve'!$H$4:$AA$16,4),"false")
C6 =IF(C4<'Qtr Lookup Box'!$A:$A,HLOOKUP(C4,'bloomberg swap
curve'!$H$4:$AA$16,4),"false")
D6 =IF(D4'Qtr Lookup Box'!$A:$A,HLOOKUP(D4,'bloomberg swap
curve'!$H$4:$AA$16,4),"false")
Where I have < I would need to use a "Like" or "=" formula. Because it is
not a number I am referring to but a date, then I don't know if it is
intelligent enough to treat it as a pure match "=" formula. I am trying to
test if C4 is within a lookup table, if the value is there then it would
return the appropriate value of the Hlookup formula, if false then for now I
have it written to return "false". I haven't been able to return a good
"true" answer within the if formula so I wasn't wasting time right now to
create the "false" response.

The external table (Qtr lookup Box) is just a row of dates by quarter end
dates 31-Mar-2005 / 30-Jun-2005 / 30-Sep-2005 / 31-Dec-2005 etc. for 10 years
out in row format (1 column).

The ideal goal is if the date is a quarter end date to look up the real # in
the bloomberg swap curve tab. If the date is not the quarter end date then
it would take the prior month rate +( 2 months forward less prior month )
divide by 3.
If H6 =G6+((J6-G6)/3)

If I try this formula in D6 I get a circular reference.
=IF(D4'Qtr Lookup Box'!$A:$A,HLOOKUP(D4,'bloomberg swap
curve'!$H$4:$AA$16,4),C6+((F6-C6)/3))

This is a snipit of the swap curve tab:
the first two rows of my past did not line up perfect here but you should
get the gist. I've listed the row and column associations. (there were
hidden columns)

A C H I J
05/09/05
4 Current Mar-05 Jun-05 Sep-05
5 Prime 6.00% 5.75% 6.25% 6.75%
6 Fed Funds 3.00% 2.75% 3.25% 3.75%
7 1 Month Libor 3.09% 2.91% 3.30% 3.72%
8 3 Month Libor 3.25% 3.10% 3.44% 3.78%
9
10 1 Year Libor 3.80% 3.74% 3.83% 3.99%
11 2 Year Swap 4.10% 4.14% 4.05% 4.16%
12 3 Year Swap 4.23% 4.37% 4.18% 4.26%
13 4 Year Swap 4.32% 4.51% 4.26% 4.34%
14 5 Year Swap 4.41% 4.65% 4.35% 4.42%
15 7 Year Swap 4.53% 4.79% 4.48% 4.54%
16 10 year Swap 4.69% 4.98% 4.65% 4.71%

So if the rate is not listed in this table then the formula should be a
blended average of the month past and forward.

I hope this was clear and detailed so that I can soon find a resolution.

Thank you, Victoria
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
Grouped Sheets and Formating Pank Mehta Excel Discussion (Misc queries) 3 March 24th 05 02:42 AM
Adding True False Results Arla M Excel Worksheet Functions 6 January 27th 05 07:29 PM
Grand Totals @ Same Place Amber M Excel Worksheet Functions 2 December 30th 04 08:13 PM


All times are GMT +1. The time now is 12:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"