Other option for IF(AND( ))
 

Dear sir,
There are 2 data sets is assumed running in A1 down, viz.:
(i) In A1 down is showing a set of date: 2009.03.26; 2012.12.31 and 2015.12.
31
(ii) In the cell from B1 to B3 showing the interes rate: 5%, 7% and 3%.

In the cell of A10, it contains a parameter, date: 2013.04.18, whereas in
the cell of B10 showing 7.00%. B10 contains an Excel formular to show the
rate when it detects the date in A10 as follow:
=IF(A10="","",IF(AND(A$1<A10,A$2A10),B$1,IF(AND(A $2<A10,A$3A10),B$2,0)))
As you can see from the formula, it covers two pair of date/rate range.

My question is that if I want the excel formular in B10 cover more date/rate
e.g. from A1 to A9 and from B1 to B9, the formula will be very long, because
the formular need to check the whole range of the date from A1 to A9. As a
result, I am thinking is there other way to achieve the same result with a
much shorter formular.

Thanks for your advice,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200903/1

Other option for IF(AND( ))
 

Hi

=LOOKUP(A10;A1:A9;B1:B9)

Regards,
Per

"wilchong via OfficeKB.com" <u43231@uwe skrev i meddelelsen
news:93aecba191a5f@uwe...
Dear sir,
There are 2 data sets is assumed running in A1 down, viz.:
(i) In A1 down is showing a set of date: 2009.03.26; 2012.12.31 and
2015.12.
31
(ii) In the cell from B1 to B3 showing the interes rate: 5%, 7% and 3%.

In the cell of A10, it contains a parameter, date: 2013.04.18, whereas in
the cell of B10 showing 7.00%. B10 contains an Excel formular to show the
rate when it detects the date in A10 as follow:
=IF(A10="","",IF(AND(A$1<A10,A$2A10),B$1,IF(AND(A $2<A10,A$3A10),B$2,0)))
As you can see from the formula, it covers two pair of date/rate range.

My question is that if I want the excel formular in B10 cover more
date/rate
e.g. from A1 to A9 and from B1 to B9, the formula will be very long,
because
the formular need to check the whole range of the date from A1 to A9. As
a
result, I am thinking is there other way to achieve the same result with a
much shorter formular.

Thanks for your advice,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200903/1

Other option for IF(AND( ))
 

use commas (,) instead of semicolons (;). Be mindful that the dates must be
in ascending order.

"Per Jessen" wrote:

Hi

=LOOKUP(A10;A1:A9;B1:B9)

Regards,
Per

"wilchong via OfficeKB.com" <u43231@uwe skrev i meddelelsen
news:93aecba191a5f@uwe...
Dear sir,
There are 2 data sets is assumed running in A1 down, viz.:
(i) In A1 down is showing a set of date: 2009.03.26; 2012.12.31 and
2015.12.
31
(ii) In the cell from B1 to B3 showing the interes rate: 5%, 7% and 3%.

In the cell of A10, it contains a parameter, date: 2013.04.18, whereas in
the cell of B10 showing 7.00%. B10 contains an Excel formular to show the
rate when it detects the date in A10 as follow:
=IF(A10="","",IF(AND(A$1<A10,A$2A10),B$1,IF(AND(A $2<A10,A$3A10),B$2,0)))
As you can see from the formula, it covers two pair of date/rate range.

My question is that if I want the excel formular in B10 cover more
date/rate
e.g. from A1 to A9 and from B1 to B9, the formula will be very long,
because
the formular need to check the whole range of the date from A1 to A9. As
a
result, I am thinking is there other way to achieve the same result with a
much shorter formular.

Thanks for your advice,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200903/1