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
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.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
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
so do you want a completely different formula in R2, one that ignores I2 and Q2?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
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
Last edited by Ace_XL; 01-31-2020 at 05:17 PM.
Life's a spreadsheet, Excel!
Say thanks, Click *
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.
did what Ace gave you solve it for you or do you still need a formula?
Thank you very much to all the contributors. The formula provided by Ace_XL works lovely
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks