SOLVED

Highlighted
New Contributor

# Formula help

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?

11 Replies
Highlighted

# Betreff: Formula help

=IF(B45="","1",IF(O45="Musikmaster Online","1","0"))

Nikolino
I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
* Beware of scammers posting fake support numbers here.
Highlighted
Best Response confirmed by rosaria93 (New Contributor)
Solution

# Betreff: Formula help

@Nikolino- thank you so much - I've been at that for 3 hours - Thank you thank you!

Highlighted

# Re: Formula help

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.

Highlighted

# Betreff: Formula help

I guess

``=(B45="")*(O45="Musikmaster Online")``
Highlighted

# Re: Formula help

@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"))

Highlighted

# Betreff: Formula help

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.

Highlighted

# Re: Formula help

That's correct, but why do we need all these extra IF, AND, SIGN in formula?

Highlighted

# Re: Formula help

Thank you @Peter Bartholomew

This is working perfectly

Highlighted

# Betreff: Formula help

You welcome
I was pleased that it led to a solution for you.

Nikolino
I know I don't know anything (Socrates)
Highlighted

# Re: Formula help

I did it to avoid having to explain why '*' and 'AND' are equivalent.  Sheer cowardice!

Highlighted

# Re: Formula help

@Sergei Baklan

"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 @Nikolino like myself were responding in a way the solution would meet @rosaria93's way of approaching it.  I think both @Peter Bartholomew  and @Nikolino 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.