Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 514
  • Last Modified:

MS Excel, formular with blank cells.

I have an excel file (attached),  the column H shows #VALUE.
I have to move cursor to F2 then press delete key to delete nothing, then the column H show correct data.


Please help me this.
Book2.xlsx
0
JameMeck
Asked:
JameMeck
2 Solutions
 
Rory ArchibaldCommented:
You could simply change the formula in H2 to:
=A2-N(D2)+N(F2)
and copy it down.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

where does the data come from? Even though the cells look empty, Excel considers some of the empty cells as text, which is the reason for the VALUE error. Some of the empty cells are seen as numbers, even if they are empty, and some of them are neither text nor numbers, i.e. just really empty.

Did you copy and paste the data from another application? That would explain the behaviour.

To repair the data, you could do the following,

In K2 enter

=IF(ISNUMBER(D2),D2,0)

copy across to N2 and down to row 17. Then copy the results and use Paste Special > Values to paste into D2. Now all empty cells will have a zero, but your formulas will calculate correctly.

You can hide the zeros with formatting.

cheers, teylyn
0
 
joaoalmeidaCommented:
Try the following in cell H2 and then copy it to the other cells

=A2-IFERROR(D2+0,0)+IFERROR(F2+0,0)
0
 
JameMeckAuthor Commented:
Thanks!

With your solutions, I could make it works now.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now