Need Assistance with #VALUE Error

Dave52

New Member
Joined
Oct 26, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
I have an issue with MS Home 2019 Excel.
I've been using this spreadsheet (and formulas) for years.

I hope this makes sense and my explanation is clear.

Rows B C D E F

All rows are numbers
D sums B & C.
E is another number.
F sums D & E.
The formulas in D & F are (basically) the same, Ex for Column F is : =IF(AND(ISBLANK(D104),ISBLANK(E104)),"",SUM(D104+E104))

My spreadsheet has 150 rows.
The first 103 rows/columns work as expected, empty cells until numbers are in B, C, D & E
Row 104 cells are empty
Suddenly at row 104 (and to 150) F has a #VALUE error.
When I "Review Formula" the evaluation keeps underlining a different area, usually underlining a "0", "N/A" or "blank".
I've checked for cell format, locked cells, etc. I can't find anything different in the remaining 104-150 cells.

TIA.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Phuoc

Well-known Member
Joined
Apr 29, 2016
Messages
522
Office Version
  1. 2016
Try this:

=IF(D104&E104="","",SUM(D104,E104))
 
Solution

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,396
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

This just suggests that D104 and/or E104 aren't really blank cells.

Possibilities:
- The cell contains space characters (or other characters that appear to be blank but aren't) - you can use the LEN() function to test the character length of the cell.
- You have formulae returning a null string "". If that's the case, you'll need to test that the cell value <> "", as ISBLANK() will return False if there is a formula in the cell.
 

Dave52

New Member
Joined
Oct 26, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Thank You for the replies.
I looked to see when I started using the spreadsheet as-is, 2010. I admit I didn't really understand the ISBLANK, heck most of the formula, but it worked so I just left it alone.
Phuoc, your suggestion works, Thanks.

Stephen, I ran LEN() on all B, C, D, E, rows 103 to 150, all came back 0.
When I ran Evaluate before, I did False as well as 0, BLANK and N/A.
At this point, I don't have an answer as to what the problem is/was, but I have learned to be glad something is fixed and forget about it. :)

Thanks Gentlemen,
Dave
 

bebo021999

Active Member
Joined
Jul 14, 2011
Messages
452
If there is "" in either 2 cells, E104 or F104, ("" comes from a formula, or type:="" into cell)

Does not work (cell + cell):
E104+F104
SUM(E104+F104)

But, work (sum range, or sum single cell):
SUM(E104:F104)
SUM(E104,F104)
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,579
Messages
5,765,211
Members
425,265
Latest member
bishopc22

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