If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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$1A10,A$2A10),B$1,IF(AND(A $2A10,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 |
#2
|
|||
|
|||
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$1A10,A$2A10),B$1,IF(AND(A $2A10,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 |
#3
|
|||
|
|||
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$1A10,A$2A10),B$1,IF(AND(A $2A10,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 |
Thread Tools | |
Display Modes | |
|
|