=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>

 

[Aladin Akyurek]

P592:

=IF(ISNUMBER(Q592),SIGN(Q592-R592),"nd")

Is this what you wanted to do?

 

[bluegold]

It doesn't accept formula?

<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;">=IF(ISNUMBER(Q592),SIGN(Q592-R592),"nd")</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>P595</td> <td>=SIGN(Q595-R595)</td></tr></tbody></table></td></tr></tbody></table>

 

[Aladin Akyurek]

It doesn't accept formula?

<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;">=IF(ISNUMBER(Q592),SIGN(Q592-R592),"nd")</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>P595</td> <td>=SIGN(Q595-R595)</td></tr></tbody></table></td></tr></tbody></table>

I hope I'm not misunderstanding your intent...

I was suggesting that you enter in P592:

=IF(ISNUMBER(Q592),SIGN(Q592-R592),"nd")

and copy it down to P595...

 

[jasonb75]

I hope I'm not misunderstanding your intent...

I was suggesting that you enter in P592:

=IF(ISNUMBER(Q592),SIGN(Q592-R592),"nd")

and copy it down to P595...

In addition to Aladin's comment above, the fact that the formula in P594 shows as a formula in the table but is absent from the formula list would indicate that P594 has been formatted as text.

 

[vasu.mk120]

In addition to Aladin's comment above, the fact that the formula in P594 shows as a formula in the table but is absent from the formula list would indicate that P594 has been formatted as text.

If your formula is display as text in work sheet Try "Ctrl+`". Press both key.

 

[jasonb75]

If your formula is display as text in work sheet Try "Ctrl+`". Press both key.

That doesn't change the format, it merely alternates the view on the whole sheet between formula and formula result.

The formula in the other cells on the table are functioning correctly, meaning only P594 has a text format as opposed to the required custom format.

 

[bluegold]

ok so what custom format do I require? All I know is when I enter Aladin's formula and press enter, all I get is the formula displaying in the table like its not accepting it?

 

[jasonb75]

ok so what custom format do I require? All I know is when I enter Aladin's formula and press enter, all I get is the formula displaying in the table like its not accepting it?

The one you set up previously, the formula =SIGN(Q592-R592) can't return Loss or Draw without additional help, so I'm guessing you have a format something like

[<0]"Loss";[>0]"Win";"Draw"

 

[bluegold]

Yes it is currently the following:
"won";"loss";"draw";@

Does this have something to do why the formula won't display "nd" when no information is displayed in column Q?