=if statement

[bluegold]

I want to set a pre IF condition that displays "nd" in column P when nothing is entered into column Q. At the moment the formula displays "draw" by default when there is no data in column Q.

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 33px;"> <col style="width: 28px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>P</td> <td>Q</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">592</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">16</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">593</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">10</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">594</td> <td style="text-align: left;">draw</td> <td style="font-size: 9pt;">
</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">595</td> <td style="text-align: left;">draw</td> <td style="font-size: 9pt;">
</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>P592</td> <td>=SIGN(Q592-R592)</td></tr> <tr> <td>P593</td> <td>=SIGN(Q593-R593)</td></tr> <tr> <td>P594</td> <td>=SIGN(Q594-R594)</td></tr> <tr> <td>P595</td> <td>=SIGN(Q595-R595)</td></tr></tbody></table></td></tr></tbody></table>

 

[bluegold]

Ok this is strange, it doesn't matter what formula I put in column P, once I press enter on the formula it doesn't calculate result like it should. All it does is display the formula I just typed in???

 

[bluegold]

Ok I worked it out, I was pasting formula into formula bar instead of just into the cell, doh pretty stupid I know.

 

[jasonb75]

The problem comes from your custom format.

"won";"loss";"draw";@

That will work if the format is applied to the cells after the formula is entered, but as you found out, applying the format, then entering, or changing the content of the cell, formula or not, causes it to display incorrectly.

The reason for this is @ in your format specifies "text", telling excel that it might look like a formula, but it's not really a formula, when in fact it is.

If you want to use the SIGN function to determine win lose or draw, using the custom format in post #9 will prevent problems like this.