Excel 2007 : Using Two IF Conditions

[sebby]

Hi Everybody,

This is my first post and hope someone will have a solution. I have spent a few hours on this, frankly it is driving me nuts, I had little success other than getting half way there.

The spreadsheet is meant to calculate revenue versus expense for the trades that I do so that I can capture each on a monthly basis, therefore at the very right the result should calculate the outcome, therefore any position that give a positive outcome should appear under a column being in this case "Gain" while the other will show those that were a "Loss".

Therefore looking at the two IF that I have put next to each other knowing it would never work, I was hoping that maybe something would come to mind and then I could just modify it accordingly.

Hoping enough information was included, kindly let me know any other details that could help providing the right solution to this headache of mine.

Thanks in advance for all that will be able to provide feedback on this!

[teylyn]

Hi sebby,

posting a workbook is always better than posting a picture of a workbook.

If you want to evaluate cells with different conditions, you need to nest the IF statements.
Change your formula

=IF(R13+AP13>0,Z13-AT13,""),IF(AL13-V13>0,AT13-Z13,"")

to

=IF(R13+AP13>0,Z13-AT13,IF(AL13-V13>0,AT13-Z13,""))

So, if the first test (R13+AP13>0) is true, the calculation (Y13-AT13) will be performed. But if it is false, the second, nested IF will be performed.

Do you see the logic?

cheers

[sebby]

Hi Teylyn,

This was something I have tried, actually I have tried quite a lot of them, the problem with that logic is there is nothing if both fails, it will be either this or that.

You have a good point that without the spreadsheet, this can be quite difficult to fully understand the outcome I am seeking. Although I have included this time the spreadsheet, kindly give me a moment to explain the way it works.

The reason I started working on this spreadsheet is that I need to keep track of all the trades that I do. I do options plays mainly spreads which mean that I will sell and buy options, I may need to close some of them therefore I need to have an Entry and Exit Result then calculate which is a gain or loss then have it show in the right column.

You will notice there is column "S" which is for "Short" in the sense of an option I sold, therefore money paid to me for doing so, should I need buy it back at a lesser amount, the net result would be a gain in my favor, should I have done it wrong, it would be a loss, which is the reason for those two columns. Also that is a reason why in both cases there is Revenue and Expense column, in the above example I would have Revenue for "S" the option and should I need to buy it back then it would be an "Expense". The IF were mean to check if one is above the other in both situation and have a different formula to be applied depending on the case. On the other hand if I buy an option it is an expense and only if I sell it back at more than I bought it then it become a gain. I really hope this makes sense, otherwise I will clarify every little bit that I can. Depending on the suggested solution, more than likely this will apply on the Stock side that I will only use for Hedging the risk.

Needless to say excel have saved me a lot of time which so far have been given back working on this particular case. This is actually so funny though so true at the same time.

Thanks in advance for your suggestions

[teylyn]

Sorry, I'm not a banker and couldn't quite follow the above.

the problem with that logic is there is nothing if both fails, it will be either this or that.

What does that mean? You don't want to show a "" result? Then maybe like this (building on the previously posted formula):

=IF(R13+AP13>0,Z13-AT13,AT13-Z13)

so, if the test is TRUE, Z13-AT13 will be returned. Otherwise AT13-Z13 will be returned.

If that is not what you want, please specify which cell you want to see a result in and explain the logic in terms of cell references, rather than banking terms.

[sebby]

Hi Teylyn,

There are two cells, there is the Gain column that should only show positive result meaning the amount I actually made, the other one being the Loss column should only show the result in terms of the amount that I lost.

The end result that I am seeking is for excel to show the gain and loss in the right column though I have no idea if in fact IF is the right function, here is the other way to look at it hoping this will help further my cause:

IF Entry Revenue > Exit Expense then calculate Net Entry + Net Exit, IF not Do Nothing
The reason for needing this is that under Gain only positive result should show since under Loss if the above if false will capture it since I will use a reverse logic once we know which is best to use.

At the same time I need the following:
IF Entry Expense < Exit Revenue then calculate Net Exit - Net Entry IF not Do nothing
Once again this is meant to capture only profit since any amount lost will be calculated in the other column.

So essentially it is checking which of the two give a positive result and then apply the correct formula based on which one does.

That is the result I would like to see, though if you have another way this could work, this would be great. I must admit that I am by no mean an expert in Excel nor do I have the most descriptive vocabulary. I know though that with open communication, we will understand each other and find a solution, there is no such as a problem, it is just a solution not found.

Thanks again and really hope this explain things better now than I previously managed to do.

[teylyn]

At the same time I need the following:

Does that mean you need that in the same cell?

Or do you want one formula if in column AX for Gain and another formula in column BB for Loss?

By the way, get rid of all these merged cells. You're not doing yourself a favour. You can size columns to be wider, but there is no reason to have the Gain column spread across 4 columns.

So, please help me understand by naming the cells where you want to see a result.
If you want to see results in two different cells, then please make that clear.

[sebby]

Hi Teylyn,

Let me apologize for the confusion this has caused. You are correct, all of the above in my previous response is meant for a single cell which in this instance would be AX13. Obviously once I know the best approach I will then copy the same thing all the way down and then do the opposite with BB13 and so forth.

Now the reason for having all the cells so small is mainly for a cosmetic reason more than a functional one. You are right saying that cells can be made wider, it is just in the past I ended having smaller cell under bigger one which ended to be the same, so I figured doing it this way from the beginning will avoid the pain of doing it later.

Thanks for your patience with me.