Hello everyone,
I need to combine three possible permutations into one formula but I always have problems with IF,ANDand OR and their parenthesis.
Also, if there is no value assigned to any of the cells the result should be a "blank", not a zero in the cell.
=IF(o5<0, AND(p5=0, AND(q5=0),o5*d5) OR
IF(o5=0,AND(p5>0, AND(q5>0),p5+q5) OR
IF(o5=0,AND(p5>0,AND(q5=0),p5*d5),"")))
I've tried but get only a variety of errors.
I am grateful for any help you can provide.
Buscador
AND() is not used this way.
the syntax is
=AND(condition, condition, condition)
You can nest AND and OR functions
=if(or(and(o5=0,p5>0,q5=0),and(o5=0,p5>0,q5=1)),something,something)
If you can describe in words what the logic is, it would be easier. It's not quite apparent from what you have posted above what you want to achieve.
Tip on working with parentheses: Always type the opening and the closing parens, then click between them and enter the content. That way you won't forget to close them.
To elaborate on npamcpp's point, I believe that is what you were trying to obtain?
=IF(AND(O5=0,P5=0,Q5=0),"",IF(AND(O5<0,P5=0,Q5=0),O5*D5,IF(AND(O5=0,P5>0,Q5>0),P5+Q5,IF(AND(O5=0,P5>0,Q5=0),P5*D5,""))))
SC
Do give a * (bottom left) if the post helped!
SCLai, thank you.Originally Posted by SCLai
I believe you got the jest of what I need but your formula brings an "#VALUE!" message. Also, do we need the last "", since the blank ("")cell is valid only if there are no values attached to "O,P,Q)?
Hello npamcpp
Thank you for the lesson in parenthesis. It helps.
I tried the following formula but it also brings a "#Value!" message
=IF(OR(AND(O5<0,P5=0,Q5=0),O5*D5),OR(AND(O5=0,P5>0,Q5>0),P5+Q5),OR(AND(O5=0,P5>0,Q5=0),P5*D5),"")
The logic is as follows:
IF cells O,P,Q, have no values, then ""
IF cell O<0, and cells P and Q=0, then cell O multiplied by cell D.
IF cell O=0, and cells P and Q>0, then cell P plus cell Q.
IF cell O=0, and cell P>0, and cell Q=0, then cell P multiplied by cell D.
Does this help?
Buscador
Sorry, but the formula works okay (at least no "#VALUE!") on my side. Perhaps you want to check on it?
Tools > Formula Auditing > Evaluate Formula
Evaluate it a step a time to see what causes #VALUE! Is one way to rectify a formula with error.
Regarding the "", it will let other cases that fail all of your "IF" checks to display blank.
i.e. If O5 > 0, regardless of the values in P5 & Q5, those values will bring you to the end of the formula.
Without the "", the cell will display 0.
Of course, this was deduced from the information you gave in the 1st post. "IF(o5=0,AND(p5>0,AND(q5=0),p5*d5),"")))"
Please simply change it to the desired output.
SC
maybe also like this..
from the formula given by SClai.
Please Login or Register to view this content.
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Vlady, thank you for letting me know and I apologize if I am braking Forum Protocol. I am not sure how everything works yet, but I wish to say thanks to npamcpp,SCLai and now vlady, for their prompt assistance. It is, in fact, very much valued by me.
Vlady and SCLai, I changed SCLai formula to what Vlady suggested and both versions now produce a blank result in all three conditions.
However, the results are added at the bottom of the column though invisible on the spreadsheet. I have checked font and security in case I made them invisible myself, but can't see anything out of the ordinary.
This is what I wrote:
SCLai's formula: =IF(AND(O5=0,P5=0,Q5=0),"",IF(AND(O5<0,P5=0,Q5=0),O5*D5,IF(AND(O5=0,P5>0,Q5>0),P5+Q5,IF(AND(O5=0,P5> 0,Q5=0),P5*D5,""))))
vlady's formula: =IF(AND(O7="",P7="",Q7=""),"",IF(AND(O7<0,P7=0,Q7=0),O7*D7,IF(AND(O7=0,P7>0,Q7>0),P7+Q7,IF(AND(O7=0,P7> 0,Q7=0),P7*D7,""))))
What can I do next?
Thanks
Buscador.
=IF(AND(O3="",P3="",Q3=""),"",IF(AND(O3<0,P3=0,Q3=0),O3*D3,IF(AND(O3=0,P3>0,Q3>0),P3+Q3,IF(AND(O3=0,P3>0,Q3=0),P3*D3,""))))
TP_1.PNG
Seems.. to be working fine?
SC
Sc,
Thank you for all the work you are putting here. I saw your spreadsheet and looks as it is supposed to be but the formula still doesn't work on my spreadsheet. Something wrong with my file. I am going to try to send mine as an Excel document. The column "R" is the one holding the formula and you can see they are blank. Also, because the cells in R column stay blank, it gives errors in other calculations (columns U and W).
I would have liked to attach the Excel file but sorry, I can't see how to do it so I'll send a picture.Template.jpg
Thanks. Maybe you can direct me where to look. The formatting of cells is correct, I believe.
Buscador
EDIT: Sorry for the double post, accidently started new post when trying to get the quotes... Please either delete or ignore this.
Thank you!
SC
Last edited by SCLai; 05-17-2012 at 04:46 AM.
I believe.. it is becasue all of them fall through those conditions.
Looking at the column O,P & Q, some those formula returns "" instead of "0", so it is just going to fall through all those conditions and end up as "".
Please try using the following formula and see if it works:
=IF(AND(O5="",P5="",Q5=""),"",IF(AND(O5<0,OR(P5=0,P5=""),OR(Q5=0,Q5="")),O5*D5,IF(AND(OR(O5=0,O5=""),P5>0,Q5>0),P5+Q5,IF(AND(OR(O5=0,O5=""), P5>0,OR(Q5=0,Q5="")),P5*D5,""))))
Hope it helps!
SC
EDIT: But please take note that what I have done was to change.. for example.. O5=0 into OR(O5=0,O5=""). Overall, the formula essencially means:
IF cells O,P,Q, have no values, then ""
IF cell O<0, and cells P and Q="" or cells P and Q = 0, then cell O multiplied by cell D.
IF cell O=0 or O="", and cells P and Q>0, then cell P plus cell Q.
IF cell O=0 or O="", and cell P>0, and cell Q=0 or Q="", then cell P multiplied by cell D.
Do notice that compared to the conditions you have stated:
Your formula for O,P & Q returns "" instead of "0", thus the blanks.
So please do check your requirements vs the new formula and see if conflicts with your interest!
Hope this helps
SC
SC, success at last. At least 2/3 success. You formula works perfectly and I thank you immensely.
Conditions 1,2 & 3:
1) IF cells O,P,Q, have no values, then ""
2) IF cell O<0, and cells P and Q="" or cells P and Q = 0, then cell O multiplied by cell D.
3) IF cell O=0 or O="", and cells P and Q>0, then cell P plus cell Q.
work perfectly.
However, the fourth condition,
=IF cell O=0 or O="", and cell P>0, and cell Q=0 or Q="", then cell P multiplied by cell D, gives a #VALUE! error.
I believe that is because the formula in "Q" column cells which is:
=IF(AND(E5="L",N5>0),(N5-L5)*100,IF(AND(E5="S",N5>0),(L5-N5)*100,""))
somehow interferes with that fourth condition.
I wanted to attach the new spreadsheet but I don't think I am allowed now.
It is a different problem so should I start another Thread with this problem?
In any case I am very grateful for your help and insight into my need.
Buscador
Last edited by Buscador; 05-17-2012 at 03:25 PM. Reason: Add attachment
Sorry, I should have checked through all the conditions before posting..
Please use this:
=IF(AND(O5="",P5="",Q5=""),"",IF(AND(O5<0,OR(P5=0,P5=""),OR(Q5=0,Q5="")),O5*D5,IF(AND(OR(O5=0,O5=""),AND(P5>0,P5<>""),AND(Q5>0,Q5<>"")),P5+Q5,IF(AND(OR(O5=0,O5=""), AND(P5>0,P5<>""),OR(Q5=0,Q5="")),P5*D5,""))))
It was caused by the fact that the formula is taking blank cells as "more than 0". Thus to the formula, P>0 even if P="".
You're welcomed, glad to be fo help
SC
SC, It worked!!! Thank you for all your time and brain power to help me.
Now, I have to figure out what you did.
He,he.
All the best.
buscador
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks