Hi, Hope you are all great!
Would someone be able to help me with the below. I have 2 formulas that work separately however want to merge them into one formula.
=IF(OR(AND(SUM(W3:X3)>=100,Y3>=0), AND(SUM(W3:X3)<=100, Y3<=0)), "Yes", "No")
=IF(W3="","N/A", IF( X3= "", "N/A", IF(Y3="", "N/A")))
I would like to add into the first formula that if W3 or X3 or Y3 are blank, then it should return "N/A"
Last edited by ariaqb; 10-26-2020 at 06:24 AM.
Welcome to the forum.
Try this:
=IF(OR(W3="",X3="",Y3=""),"N/A",IF(OR(AND(SUM(W3:X3)>=100,Y3>=0), AND(SUM(W3:X3)<=100, Y3<=0)), "Yes", "No"))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
You can try
=If(OR(W3="",X3="",Y3=""),"N/A",IF(OR(AND(SUM(W3:X3)>=100,Y3>=0), AND(SUM(W3:X3)<=100, Y3<=0)), "Yes", "No"))
TryFormula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Think we have a consensus here
Yup!
Thank you all for your help!
@Ali: quite amused by the time stamps on the thread
Would you also help me to try and add another outcome please.
So my current formula is =IF(OR(W3="",X3="",Y3=""),"N/A",IF(OR(AND(SUM(W3:X3)>100,Y3>0), AND(SUM(W3:X3)<100, Y3<0)), "Yes", "No"))
Would it be possible to add =IF(AND(SUM(W3:X3)=100,Y3=0), "Yes", "No") into the first equation.
Essentially if W3+X3 is 100 then Y3 should be 0 and vice versa.
Thank you!
Really think you should give it a go or you'll never get the hang of it.
The part we added at the beginning was to avoid any of the cells being zero. Looks like you should be adding the new bit to the back end. You might need to look at using OR given that you want to end up with Yes or No for variations on a theme.
These nested formulae are like anything complex, you need to plan them.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks