Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

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)

Mabye it needs to be something like this: (notice the qtr dates change)

=IF(AND(ExpireDate>FirstQt

~~~~~~~~~~~~~~

=IF(AND(ExpireDate>FirstQt

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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.

If( condition, value_if_true, value_if_false )

so the 1st function you provide is not completed.

=IF(AND(ExpireDate>FirstQt

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.

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

Saqib

=IF(OR(AND(ExpireDate>Firs

let me know if you need anything else.

MASTER-LIST.xlsx

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

Saqib

Copy-of-MASTER-LIST.xlsx

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$2

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.

Brad

MASTER-LISTQ26940580.xlsx

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.

<<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<DAT

This formula assumes that cell I23 contains the date the quarter

Brad

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial=IF(AND($F26>=I$23,$F26<DA

DATE(YEAR(I$23),MONTH(I$23

AND($F26>I$23,$F26<DATE(YE

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,

=IF(AND($F26>=I$23,$F26<DA

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.

Brad

=IF(AND($F26>=I$23,$F26<DA

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

Brad

MASTER-LISTQ26940580.xlsx

<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.

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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?