IF Statements Additions

[Excel-Access]

Hi Community

Earlier the Excel community helped me modify my IF statements - thank you and I marked this as solved.

Whilst preparing reports, I realise there is a different variable to consider.

Any help would be appreciated.

The details are in the attachment.

Regards

[Sam Capricci]

I found your directions a bit confusing. According to the note in cells J7 and J8 if there is a yes in J2 you want K2 and if a no in J2 you want 0 - both in R2.
these seem to negate the rest of the formula in R2 because there are only two possible values in J2.

[Excel-Access]

Hi

1. If J2 says Yes and there is value in K2, you take the K2 value which is negative and put it positive in R3 - multiplier is not used

2. If J2 says No, and there is value in K2, it will ignore that value in pout zero in R3

3. If J3 says Yes, and there is no value in K3, but a value in I3, it will take I3 x Q3 and put the value in R3

4. If J3 says No, and there is no value in K3, but a value in I3, it will put zero in R3

[Sam Capricci]

1. If J2 says Yes and there is value in K2, you take the K2 value which is negative and put it positive in R3 - multiplier is not used
2. If J2 says No, and there is value in K2, it will ignore that value in pout zero in R3

so do you want a completely different formula in R2, one that ignores I2 and Q2?

[Ace_XL]

Try in R2

=IF(J2="No",0,IF(J2="Yes",IF(K2<>"",ABS(K2),I2*Q2),""))
or

=IF(J2="No",0,IF(J2="Yes",IF(K2<>"",-K2,I2*Q2),""))
Copy down

[Excel-Access]

Hi

The current formula in R2 works, I would like to modify if possible so that it takes into consideration Yes/No, column J.

However, if a new formula would work, more than happy because currently, if there is a CTA value it takes that value, if there is no CTA value but a WP value, it takes that value x multiplier which is correct.

I wanted to give individuals to decide if CTA is required Yes/No as not all projects need it.

[Sam Capricci]

did what Ace gave you solve it for you or do you still need a formula?

[Excel-Access]

Thank you very much to all the contributors. The formula provided by Ace_XL works lovely

[Sam Capricci]

based on what you wrote these are my guesses...
in R2 =IF(AND(J2="Yes",K2<>""),ABS(K2),IF(AND(J2="no",K2<>""),0,""))
in R3 =IF(AND(J3="yes",K3="",I3<>""),I3*Q3,IF(AND(J3="no",K3="",I3<>""),0))
By the way, in your post #3 you have for items 1 and 2 the output being in R3, not R2, not sure if this is a mistake, I wrote those formulas for R2 (numbers 1 & 2) and R3 (numbers 3 & 4) from post #3.