excel if/then command

[Adam0414]

Hello I am trying to add an if/then function on my excel. Here is the basics, I have many boxes with numbers 1-10. I want to add a function that says if a number is 10 then it will be changed to 0, but if its anything else it does not change at all.

Can anyone help me out. THANK YOU!

[teylyn]

Hello Adam,

welcome to the forum.

Do you want to change the number in the original cell? That would require a VBA macro.

But if you want the number to display in the next cell, then use something like this

=if(A1=10,0,A1)

Put this formula into B1 and your number from 1 to 10 into A1

[Adam0414]

Thanks for the reply teylyn. I've been trying to do something like that. I already have =SUM(T3+2) in the formula bar. I put that formula under in the next cell. Its not changing anything, just placing a "0" in the cell that I placed your formula.

[teylyn]

Adam, how is that related to the IF?

Can you possibly post a sample workbook? Click Go Advanced below the Quick Reply box and then the paper clip icon to attach a file.

[Adam0414]

Ok thanks!

[teylyn]

Adam, maybe like this

=VALUE(IF(A3=10,0,A3)&IF(B3=10,0,B3)&IF(C3=10,0,C3))

copy down

[Adam0414]

Oh that worked very well. How can I also get the 11, 12, and 13 to add to the function without the total adding up, because if I do it now the total doubles and triples.

THANK YOU!

[teylyn]

maybe check if the number is 10 or greater:

=VALUE(IF(A3>=10,0,A3)&IF(B3>=10,0,B3)&IF(C3>=10,0,C3))

[Adam0414]

I really appreciate all the help, but I need 10=0, 11=1, 12=2, 13=3.

[teylyn]

Ok, no problem


=VALUE(IF(A3>=10,A3-10,A3)&IF(B3>=10,B3-10,B3)&IF(C3>=10,C3-10,C3))

Does that work? Should also work for 14, 15, 16, 17, 18 and so on, up to 19. Will you ever break acros 20?? Then another solution can be arranged.

[Adam0414]

WOW thank you soooo much. You really know your stuff! That worked perfectly! However one of the 10's that was suposed to change to 0 actually changed and became blank instead. Anyway of making sure it's 0 and not blank? Thanks!

[teylyn]

Which one changed to blank? In you sample workbook? Which cell?

The formula does not do blanks. It will return either what's in the original cell or the original cell minus 10. I'd like to see where it will produce blanks, so please upload your file with the formula applied.

[Adam0414]

Hey I figured it out, just right clicked the cells and went format cell then custom and made it 3 digits. Thanks for all your help teylyn! I'm sure I'll be back soon with more and more questions.

[teylyn]

Feel free to do so. There's a lot of help to be had in this forum!

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[teylyn]

Acktshully:

you can use this and get rid of all the IFs

=MOD(A3,10)&MOD(B3,10)&MOD(C3,10)