Replace all 1's with 0's, and all 0's with 1's

[thomexcel]

Ok, so I would like to know if Exel has a function, that can replace, all 1's with 0's, and all 0's with 1's, at once.

[Tommy90]

You can use Find and Replace (Ctrl + H)
First Replace all 0's with - say 2
Then replace all 1 to 0,
then replace all 2 to 1

[thomexcel]

The problem is I have to do it at once. I have a formula with some references, like =C3+E3, and then I need Excel to replace the C with E, and then the E with C.

[Tommy90]

The problem is I have to do it at once. I have a formula with some references, like =C3+E3, and then I need Excel to replace the C with E, and then the E with C.

I do not understand.
Say =C3+E3

and then I need Excel to replace the C with E

this mean? = E3+E3 ???

and then the E with C

this mean? = C3+C3 ???

Please tell us from the beginning what you want, not after receiving an answer, that you actually want something else.

[thomexcel]

The idea I had before in the other thread, is not the same as this. What I need to do is I want to for example have Excel to change a formula, called

=IF(Matches!$D$3='Group Point Counter'!$C$3,Matches!$E$3,)+IF(Matches!$D$4='Group Point Counter'!$C$3,Matches!$E$4,)+IF(Matches!$D$5='Group Point Counter'!$C$3,Matches!$E$5,)+IF(Matches!$D$6='Group Point Counter'!$C$3,Matches!$E$6,)+IF(Matches!$D$7='Group Point Counter'!$C$3,Matches!$E$7,)+IF(Matches!$D$8='Group Point Counter'!$C$3,Matches!$E$8,)+IF(Matches!$D$9='Group Point Counter'!$C$3,Matches!$E$9,)+IF(Matches!$D$10='Group Point Counter'!$C$3,Matches!$E$10,)+IF(Matches!$D$11='Group Point Counter'!$C$3,Matches!$E$11,)+IF(Matches!$D$12='Group Point Counter'!$C$3,Matches!$E$12,)+IF(Matches!$D$13='Group Point Counter'!$C$3,Matches!$E$13,)+IF(Matches!$D$14='Group Point Counter'!$C$3,Matches!$E$14,)+IF(Matches!$D$15='Group Point Counter'!$C$3,Matches!$E$15,)+IF(Matches!$D$16='Group Point Counter'!$C$3,Matches!$E$16,)+IF(Matches!$D$17='Group Point Counter'!$C$3,Matches!$E$17,)+IF(Matches!$D$18='Group Point Counter'!$C$3,Matches!$E$18,)+IF(Matches!$D$19='Group Point Counter'!$C$3,Matches!$E$19,)+IF(Matches!$D$20='Group Point Counter'!$C$3,Matches!$E$20,)+IF(Matches!$D$21='Group Point Counter'!$C$3,Matches!$E$21,)+IF(Matches!$D$22='Group Point Counter'!$C$3,Matches!$E$22,)+IF(Matches!$D$23='Group Point Counter'!$C$3,Matches!$E$23,)+IF(Matches!$D$24='Group Point Counter'!$C$3,Matches!$E$24,)+IF(Matches!$D$25='Group Point Counter'!$C$3,Matches!$E$25,)+IF(Matches!$D$26='Group Point Counter'!$C$3,Matches!$E$26,)+IF(Matches!$D$27='Group Point Counter'!$C$3,Matches!$E$27,)+IF(Matches!$D$28='Group Point Counter'!$C$3,Matches!$E$28,)+IF(Matches!$D$29='Group Point Counter'!$C$3,Matches!$E$29,)+IF(Matches!$D$30='Group Point Counter'!$C$3,Matches!$E$30,)+IF(Matches!$D$31='Group Point Counter'!$C$3,Matches!$E$31,)+IF(Matches!$D$32='Group Point Counter'!$C$3,Matches!$E$32,)+IF(Matches!$D$33='Group Point Counter'!$C$3,Matches!$E$33,)+IF(Matches!$D$34='Group Point Counter'!$C$3,Matches!$E$34,), and I need to change all E's to D's, and all D's, to E's, and I have like 20 of those formula, where I have to replace D's with E's, and E's with D's.

[scottiex]

i think you need to upload a file with examples that cover the full range of your requirements.

Also consider can you use VBA or do you need a manual method.

otherwise if i understand your latest post correctly - you can do it in a 3 step process as per his example - just replace
$C$ with $F$
then $E$ with $C$
and $F$ with $E$

if it is just swapping C and E's

[thomexcel]

i think you need to upload a file with examples that cover the full range of your requirements.

Also consider can you use VBA or do you need a manual method.

Okay I can upload a sample, but it may be a confusing excel, but thank you for helping me . And I can use VBA's.

The reason I have to replace them, is that I have one formula that counts the Goals For, but I need one that counts, the Goals Against

[thomexcel]

i think you need to upload a file with examples that cover the full range of your requirements.

Also consider can you use VBA or do you need a manual method.

otherwise if i understand your latest post correctly - you can do it in a 3 step process as per his example - just replace
$C$ with $F$
then $E$ with $C$
and $F$ with $E$

if it is just swapping C and E's

Hey I think that works, thanks a lot . Thank you for trying to help me, even though I was bad explainer.

[thomexcel]

Sorry, again, but why does Excel say this formula equals False? =IF(Matches!$D$3='Group Point Counter'!$C$3,IF(Matches!$E$4>Matches!$G$4;1))

[scottiex]

what is he HVIS function? Do you mean "If"?
what cell do you see it in now?
your formula seems to be mixing two different languages / formats.

[thomexcel]

what is he HVIS function? Do you mean "If"?
what cell do you see it in now?
your formula seems to be mixing two different languages / formats.

No, it's just because I change HVIS to IF, so it is in english here at the forum, I just forgot to do it with that one, but on my excel the formula is =HVIS(Matches!$D$3='Group Point Counter'!$C$3;HVIS(Matches!$E$4>Matches!$G$4;1). So I havent mixed languages in the formula

It is not smileys I want to make.

[scottiex]

IF(A=B,IF(C>D,1))

so if A <>B and C not > D, then false.

maybe you want something like

Please Login or Register  to view this content.

where 0 is your substitute for false.

[thomexcel]

Thanks for helping, but I dont understand this

so if A <>B

[scottiex]

exactly.. so it doesnt go to the first option it goes to the second one that you have not supplied - hence false

If statement format is
if(check statement, if true, if false and if you dont enter this then just "false")

[thomexcel]

IF(A=B,IF(C>D,1))

so if A <>B and C not > D, then false.

maybe you want something like

Please Login or Register  to view this content.

where 0 is your substitute for false.

Thank you very much

[scottiex]

OK mark the thread as solved and assign any * to anyone who helped.

My second part of post #6 of course just rephrased what tommy90 was already telling you to do based on the new info

[FDibbins]

Your title says swap 0's and 1's, but in 1 post you refer to C's and E's , then provide a formula and say you want to swap D's and E's

Can you explain exactly what you are doing, because I have a feeling there is a MUCH simpler way of doing what that beast of a formula in post #5 is doing.
Maybe even this?
=SUMIF(Matches!$D$3:$D$34,'Group Point Counter'!$C$3,Matches!$E$3:$E$34)

[thomexcel]

Do you know why is Excel saying this is wrong? =IF(Matches!C3=H2H!D3(AND(Matches!G3=H2H!H3,Matches!D3),0,)

It says that it is missing a right ) or left (

[scottiex]

3 open brackets and only 2 close brackets.
maybe you should explain in words what you want it to do.

[FDibbins]

1. Again, can you explain what you are doing?
2. Did you try my suggested formula
3. Your formula is almost right...
=IF(and(Matches!C3=H2H!D3,Matches!G3=H2H!H3),Matches!D3,0)