# If statement - And / Or

Experts, I need to modify the below to include another statement in this but not sure if it si AND or OR.  The below works but you can see it does not include the ThirdQtrDate and FourthQtrDate.  I dont know the next step.  I think it is an OR statement. Not sure how to explain because I dont know these nested if statement well.  If someone can respond with an answer I can test and go from there step by step.

Mabye it needs to be something like this: (notice the qtr dates change)
=IF(AND(ExpireDate>FirstQtrDate,IssueDate<SecondQtrDate),OR (ExpireDate>ThirdQtrDate,IssueDate<FourthQtrDate),
~~~~~~~~~~~~~~

=IF(AND(ExpireDate>FirstQtrDate,IssueDate<SecondQtrDate),IF(\$G153="USD",\$H153,\$H153*VLOOKUP(\$G153,\$H\$4:\$I\$18,2,0)),0)
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
http://www.experiglot.com/2006/12/11/how-to-use-nested-if-statements-in-excel-with-and-or-not/
Would you be able to provide a excel worksheet?
Commented:
The If syntex is the following:
If( condition, value_if_true, value_if_false )

so the 1st function you provide is not completed.
=IF(AND(ExpireDate>FirstQtrDate,IssueDate<SecondQtrDate),OR (ExpireDate>ThirdQtrDate,IssueDate<FourthQtrDate),

You could try
=IF(OR(AND(B1=1, C1=2), AND(B1=2, C1=1)), "Yes", "no")

Just replace, B1 = 1, c1=2, b1=2, c1=1 with your conditoin and replace "yes", "no" with your result for true and false.
EngineerCommented:
It would be easier to help you if you could upload a spreadsheet with the quarter dates in their actual positions and a few examples which cover most cases.

Based on your previous question, it might be possible to propose a solution by a method different from what you already have.

Saqib
Project financeAuthor Commented:
this worksheet is a monster to me at least.  I will do so and try to tone it down.
Mechanical EngineerCommented:
Try a combination of OR and AND functions for your quarter comparisons:
=IF(OR(AND(ExpireDate>FirstQtrDate,IssueDate<SecondQtrDate),AND(ExpireDate>ThirdQtrDate,IssueDate<FourthQtrDate)),IF(\$G153="USD",\$H153,\$H153*VLOOKUP(\$G153,\$H\$4:\$I\$18,2,0)),0)
Project financeAuthor Commented:
OK I have it attached.  I pared it down to only 1 row with columns.  I have an explanation in the excel file.

let me know if you need anything else.
MASTER-LIST.xlsx
EngineerCommented:
Checkout this file.

For the sake of checking I have entered a few other currencies as well. I assume that the rate shall be different for every quarter and have inserted some of the values for the other quarters.

I have entered the figure whenever the expiry date lies within the quarter. This has to be approved by you as you have not defined it in the problem description.

Saqib
Copy-of-MASTER-LIST.xlsx
Mechanical EngineerCommented:
Based on the sample workbook, it seems to me as though only the LC expiration date needs to be considered. If it falls within a quarter, then you want to return its value. Otherwise, the formula should return 0.

Assuming that the dates in I23:L23 are the beginning of each quarter, then you might use a formula for cell I26 like:
=IF(AND(\$F26>I\$23,\$F26<J\$23),IF(\$G26="USD",\$H26,\$H26*INDEX(I\$4:I\$19,MATCH(\$G26,\$H\$4:\$H\$19,0))),0)

As you copy this formula across, it eventually assigns the full amount of the LC to the quarter beginning 4/1/2012. Changing from VLOOKUP to INDEX and MATCH allows the foreign currency exchange rate to be grabbed automatically.

MASTER-LISTQ26940580.xlsx
Project financeAuthor Commented:
thank you both for the responses.  I like them both.

I sorta inherited the spreadsheet and I thought that the expiration Date is what the criteria should be built off of.  Now that I know this, Saqib, I think your formula should take into account only the expiration date.  I think your formula includes the issue date.  MIght end up with the same result though.

Byundt, very fancy formula.  I added some rows of data and not sure but based on teh expiration date the amount did not seem to appear in the correct column.  Maybe you can confirm this.  I probably did it wrong.  The columns MNOP would not need to be in the formula.  The file was pared down and I am only dealing with the current year (there is other data in the columns MNOP).  If not in the current year then a 0 would return.

thank you.
Mechanical EngineerCommented:
pdvsa,
<<but based on teh expiration date the amount did not seem to appear in the correct column>>
This is one of those questions where a few examples along with the right answers would make it easy to write the formula. Both ssaqibh and I are guessing.

I can eliminate the dependence on columns M:P with the following tweak to my previous formula (it borrows ssaqibh's DATE approach). Put it in cell I26 and copy across.
=IF(AND(\$F26>I\$23,\$F26<DATE(YEAR(I\$23),MONTH(I\$23)+3,1)),IF(\$G26="USD",\$H26,\$H26*INDEX(I\$4:I\$19,MATCH(\$G26,\$H\$4:\$H\$19,0))),0)
This formula assumes that cell I23 contains the date the quarter begins. It isn't clear from your workbook whether that should be the case--but the first of the month is an odd end date for a quarter.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Mechanical EngineerCommented:
I should have been using >= for the beginning test. This is the corrected formula for cell I26:
=IF(AND(\$F26>=I\$23,\$F26<DATE(YEAR(I\$23),MONTH(I\$23)+3,1)),IF(\$G26="USD",\$H26,\$H26*INDEX(I\$4:I\$19,MATCH(\$G26,\$H\$4:\$H\$19,0))),0)

DATE(YEAR(I\$23),MONTH(I\$23)+3,1)     returns a date exactly 3 months from the date in I23
AND(\$F26>I\$23,\$F26<DATE(YEAR(I\$23),MONTH(I\$23)+3,1))     TRUE if LC expires on or after I23 and before 3 months later
MATCH(\$G26,\$H\$4:\$H\$19,0)     returns index number where G26 matches ones of the currency types (1 = first row, 2 = second, etc.)
INDEX(I\$4:I\$19,MATCH(\$G26,\$H\$4:\$H\$19,0))      returns a value from column I on same row as matches the currency type
=IF(AND(\$F26>=I\$23,\$F26<DATE(YEAR(I\$23),MONTH(I\$23)+3,1)),IF(\$G26="USD",\$H26,\$H26*INDEX(I\$4:I\$19,MATCH(\$G26,\$H\$4:\$H\$19,0))),0)      returns 0 if LC expires in any other quarter than one beginning on I23 date. Returns the value of the LC adjusted for exchange rates if LC denominated in any other currency.

The use of \$ before certain column letters and row numbers was deliberate. It fixes those letters or numbers while allowing other references to adjust automatically as you copy the formula across and down. In other words, no need to make any adjustments.

Mechanical EngineerCommented:
One additional problem that you will run into is due to all the 0 values in your exchange rate chart. If the LC expires in a future quarter this year and it is denominated in something other than USD, then my formula will grab the 0 from your table.

If you replace those 0 with blanks for all future dates, you can make the formula use the most recent data with (once again for cell I26):
=IF(AND(\$F26>=I\$23,\$F26<DATE(YEAR(I\$23),MONTH(I\$23)+3,1)),IF(\$G26="USD",\$H26,\$H26*LOOKUP(1E+307,INDEX(\$I\$4:I\$19,MATCH(\$G26,\$H\$4:\$H\$19,0),))),0)

The INDEX function is now returning a row of data (for the matching currency type). This row starts with column I, and ends with the same column as contains the formula. The apparently extra comma at the end of the INDEX function means to return all columns in that row.

The LOOKUP function then grabs the most recent data from that row of data. If you have exchange rate data for that quarter, LOOKUP will use it. If not, LOOKUP returns the most recent data that is available. The 1E307 is a very large number--LOOKUP won't find it, and so returns the last value (of the same data type) that it did find.

MASTER-LISTQ26940580.xlsx
Project financeAuthor Commented:
<If you replace those 0 with blanks for all future dates,
Do you mean the 0's in the foreign exchange rates?
like .000434  ....I am not sure how I could do that.  Probably dont follow.

Mechanical EngineerCommented:
Look at the sample workbook I posted. It is just the future foreign exchange rates that need to be blanked.
Project financeAuthor Commented:
oh I see now.  thank you..
Mechanical EngineerCommented:
Note that I put the suggested formula in row 27 and copied it across. I didn't want to disturb the formulas in row 26 in case I needed to refer to them. If you like the results, however, you will put the suggested formula in cell I26 and copy across and down.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.