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

Patryd

New Member
Joined
Dec 11, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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)
 
Upvote 0
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
    Capture.JPG
    68.9 KB · Views: 4
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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
    Capture.JPG
    77.6 KB · Views: 4
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top