#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)
 

Some videos you may like

Excel Facts

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 11, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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: 2

Patryd

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

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 11, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top