# #value displayed in a cell that also had #n/a

#### Patryd

##### New Member
HI, need some help with this. This formula removed the #n/a from the cell but it now displays #value. What do I need to add to the formula so that it displays a blank instead of #value

=IF(ISNA(I6),0,I6)-IF(ISNA(G6),0,G6)

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Joe4

Welcome to the Board!

Please let us know exactly what is in (or may be in) cells I6 and G6, and want you want the formula to return when one or both of those values are not numeric.

This would turn any errors in those cells to 0:
Excel Formula:
``=IFERROR(I6,0)-IFERROR(G6,0)``

#### Patryd

##### New Member
Welcome to the Board!

Please let us know exactly what is in (or may be in) cells I6 and G6, and want you want the formula to return when one or both of those values are not numeric.

This would turn any errors in those cells to 0:
Excel Formula:
``=IFERROR(I6,0)-IFERROR(G6,0)``
Hi Joe,

Thanks. I am somewhat of an Excel rookie, but I do like playing around in it. This is for a poker spreadsheet that i am keeping for my league. G6 is a cell with this formula =C6+D6+E6+F6 that totals the amount of buys ins for a player, it is a numerical \$ value. I6 is a cell that has a drop down box with 5 separate options (vlookup) that inserts certain percentages depending on where the player finished in the game.

#### Attachments

• Capture.JPG
68.9 KB · Views: 2

#### Patryd

##### New Member
Welcome to the Board!

Please let us know exactly what is in (or may be in) cells I6 and G6, and want you want the formula to return when one or both of those values are not numeric.

This would turn any errors in those cells to 0:
Excel Formula:
``=IFERROR(I6,0)-IFERROR(G6,0)``
Hi Joe, I should also mention that I have the if(isna code in the re because originally I was getting the #n/a error.

#### Patryd

##### New Member

Welcome to the Board!

Please let us know exactly what is in (or may be in) cells I6 and G6, and want you want the formula to return when one or both of those values are not numeric.

This would turn any errors in those cells to 0:
Excel Formula:
``=IFERROR(I6,0)-IFERROR(G6,0)``

#### Patryd

##### New Member
That code works okay if I make a selection in the drop down box as it returns a number, however if I leave the drop down box blank, that is when I get the #value error

#### Joe4

Does the formula I posted in my first reply do what you want?
If not, please provide an example of when it is not doing what you want (and what your expected value in that example is).

#### Patryd

##### New Member
Does the formula I posted in my first reply do what you want?
If not, please provide an example of when it is not doing what you want (and what your expected value in that example is).
Hi Joe,
No, it does not fix the problem. I would like for column J to display blank or 0 instead of the #value. In the attached image you can see that column H has a drop down box with selections based on percentages. When a selection is made there is no error in column J. But when the drop down box is left blank, column J returns the #value error.

#### Attachments

• Capture.JPG
77.6 KB · Views: 2

#### Joe4

I would like for column J to display blank or 0 instead of the #value.
Unless, I missed it somewhere, I don't believe you have told me what the formula in column J is yet.

#### Patryd

##### New Member
This is the formula in column J, =IF(ISNA(I6),0,I6)-IF(ISNA(G6),0,G6). The IF(isna helped remove the #n/a, but resulted in the #value

Replies
2
Views
288
Replies
7
Views
112
Replies
7
Views
124
Replies
1
Views
154
Replies
2
Views
176

1,127,604
Messages
5,625,755
Members
416,133
Latest member
ToseSenpai

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back