Aug 10 2020 04:21 AM
Hi smart excel people,
I need help with a formula. I want to return the number 1 only if cell 1 is blank AND cell 2 equals and exact text, otherwise return 0 or blank. I can make each separate part work independently
=IF(B45 = "", 1)
=IF(O45="Music Master Online", 1)
But when I try and join them together, the closest I've got to is
=IF(B45="",AND(O45="Music Master Online"),"1")
This formula returns TRUE or FALSE - How do I make it return the number 1 or 0 instead?
Aug 10 2020 04:36 AM
Aug 10 2020 04:39 AM
Solution@NikolinoDE- thank you so much - I've been at that for 3 hours - Thank you thank you!
Aug 10 2020 04:41 AM
If both conditions are satisfied the formula returns the number 1, else 0.
= SIGN( AND( cell1<>"", cell2="Music Master Online" ) )
Without the SIGN it would return TRUE or FALSE. The cells refer to your directly referenced cells, except that I moved them.
Aug 10 2020 04:42 AM
Aug 10 2020 04:49 AM
@rosaria93 You have a choice, they both do the same thing. Seeing as AND without will return the TRUE / FALSE (which can still work for you) the SIGN function return it as 1 / 0 respectively.
=IF(AND(B45="",O45="Music Master Online"),1,0)
or
=SIGN(AND(B45="",O45="Music Master Online"))
Aug 10 2020 04:51 AM
unfortunately the formula is still not 100% correct.
eg If my first cell is blank and my second cell has "different text" , it returns 1. It should return 0.
Aug 10 2020 04:55 AM
That's correct, but why do we need all these extra IF, AND, SIGN in formula?
Aug 10 2020 05:04 AM
Aug 10 2020 05:07 AM
I did it to avoid having to explain why '*' and 'AND' are equivalent. Sheer cowardice!
Aug 16 2020 09:09 AM
"That's correct, but why do we need all these extra IF, AND, SIGN in formula?"
Who said they are needed ?
Because @rosaria93 was already applying the logic of IF, I believe @Peter Bartholomew and @NikolinoDE like myself were responding in a way the solution would meet @rosaria93's way of approaching it. I think both @Peter Bartholomew and @NikolinoDE provided a good approach to answer the question. Which one works best and makes the most sense is down to @rosaria93, not to any of us. As long as we are all providing positive support and the answer is correct I will not judge another person's approach to the solution.
Aug 10 2020 04:39 AM
Solution@NikolinoDE- thank you so much - I've been at that for 3 hours - Thank you thank you!