Combine 2 completely different if statements

[ajanson]

Hello,

I'm having trouble combining two if statements.

Column K has A for active and T Groups. Column L has 2015 monthly premiums and Column M has 2016 monthly premiums. Finally Column R shows the 2015 Monthly loss.

I need a formula to show 2 things:

1. If column K2="T" then copy the contents of L2 into R2 AND
2. M2 minus L2 - but only display the negative numbers

For 1. I have =IF(K2="T",L2,"")
for 2. I have =IF(M6-L6>0,0,M6-L6)

But i can't combine the 2 together!

Also, I would also like to show a blank in column R if a cell in column M has no figure (we haven't gotten the 2016 monthly premium as yet).

If anyone can help me I would greatly appreciate it!

[BadlySpelledBuoy]

So you want two values in the same cell or does one of your formulas override the other? If so, which?

Some sample data with expected results is always a good idea if you need a solution quickly.

BSB

[ajanson]

I guess I want 2 values in the same cell. I'm trying to calculate the net loss. So, if a group is listed as "T" in column K, then i want the negative number in column L (2015 premium) to show in Column R. At the same time, I want a calculation in column R to show the negative number from subtracting 2016 premiums (M) and 2015 premiums (L). And then ultimately sum column R to show the net loss.
K L M N O P Q R
Active (A) /Terminated (T) 2015 Monthly 2016 Monthly 2015 Annual 2016 Annual 2015 Monthly Gain 2015 Annual Gain 2015 Monthly Loss 2015 Annual Loss
A $500.00 $700.00 $6,000.00 $8,400.00 $200.00 $2,400. $-
T ($1000.00) ($12,000.00) ($1,000.00)
A $6,000 $3,000 $72,000 $36,000 ($3,000) ($36,000) ($3,000)

Hope this is clear. Thanks for all your help!

[ajanson]

I'm sorry i had it all spaced out nicely, and then after submitting, all my spaces were removed.

[BadlySpelledBuoy]

I'm sorry i had it all spaced out nicely, and then after submitting, all my spaces were removed.

Yeah, it does that.
Pop the data in a sample workbook and attach that. FAR easier for all that way.

BSB

[ajanson]

I'm so sorry, I'm can't find the attachment option! Sorry, this is my first time attaching

[BadlySpelledBuoy]

No worries. The stock response below should help:

BSB



Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[ajanson]

Please see attached and thank you very much.

[BadlySpelledBuoy]

Sorry, even after looking at the workbook and reading again your explanation in post#3, I'm still at a loss as to what you require here.

Could you show your expected result and breakdown how you arrive at that result?

BSB

[ajanson]

I'm sorry this is so confusing...

1. If there is a T then I want to copy the contents from "2015 Monthly" into "2015 Monthly Loss" - in the example workbook, it would be the amount under M3 to show under S3.
As well
2. I would also like to show the loss that is derived from subtracting "2015 Monthly and 2016 monthly". I want to see this also in column S.

For 1. I have =IF(K3="T",M3,0)
for 2. I have =IF(N3-M3>0,0,N3-M3)

I guess its hard to explain. Thanks for trying anyway.

[ajanson]

I'm trying to combine it under the S column like this:

=IF(K3="T",M3,0,OR(N3-M3>0,0,N3-M3))

But that doesn't work

[BadlySpelledBuoy]

If you change the OR in that formula for IF does it do what you need?

Please Login or Register  to view this content.

BSB

[ajanson]

Well thank you very much! It's kind of sad that that's all I had to do

[ajanson]

Would you happen to know what I could put down to show a blank in S if the 2016 monthly cell is blank (like if we don't have the figure yet) and instead of putting in the 2015 monthly figure under S it's just blank.

Like isblank(m3)

[BadlySpelledBuoy]

Only easy when you know how!
You were close though. Almost got there without help, but sometimes we are too close to a problem to see the simple solution.

Glad it's all sorted now.

BSB

[BadlySpelledBuoy]

Would you happen to know what I could put down to show a blank in S if the 2016 monthly cell is blank (like if we don't have the figure yet) and instead of putting in the 2015 monthly figure under S it's just blank.

Like isblank(m3)

=IF(M3="","",YourFormulaHere) would be one way.

BSB