File Formatting is Preventing Correct Formula Calculation

There is something peculiar about the spreadsheet named in this file as "Original".  The way the cells are formatted or populated seem to prevent any formulas from working correctly.

I noticed, however, that if I populate the empty cells (being addressed in the formula) with zero then formulas work.

The Tab named "Original" is the bad file that I get from my supplier.  The other tab named "New COLS…" is the file I manually and painstakenly repaired and entered SUM formulas in COL AG-AK.

I get very large files of this type from a supplier on a regular basis so I really need an immediate solution.  Perhaps I can run a macro that will populate all the empty cells in a selected area with "0"?  Thanks for your help with this, Experts!
Cell-Fomat-not-accepting-formulas.xlsx
thutchinsonAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Neil RussellConnect With a Mentor Technical Development LeadCommented:
Column AA should be
=IF(AND($V2="SSS",$W2="Sterling"),$R2,0)

and AD should be
=IF(AND($V2="SSS",$W2="Sterling"),$R2,0)

The problem was the "" at the end and not a 0
0
 
Ejgil HedegaardConnect With a Mentor Commented:
Or use =SUM(X2,AA2,AD2) in AG2 if you want the cells in X2, AA2 and AD2 to look empty.
The SUM function use the value 0 for text.
0
 
Rob HensonFinance AnalystCommented:
Ejgil's suggestion then begs the question, if =SUM(number,text,text) works why doesn't  =number+text+text

Excel is effectively doing the same thing. It is including text in a sum and ignoring it.

Thanks
Rob H
0
 
Rory ArchibaldCommented:
The + operator just doesn't work the same way - it won't ignore any of its operands, whereas SUM can ignore its parameters. It's actually very useful to have the + operator work that way - it allows you to do things like coercing string representations of numbers/dates/booleans to numeric values.
0
 
thutchinsonAuthor Commented:
Good stuff, thanks guys.   I really appreciate it.
0
All Courses

From novice to tech pro — start learning today.