Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# File Formatting is Preventing Correct Formula Calculation

Posted on 2014-12-04
Medium Priority
75 Views
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
0
Question by:thutchinson
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 37

Accepted Solution

Neil Russell earned 1000 total points
ID: 40481586
Column AA should be
=IF(AND(\$V2="SSS",\$W2="Sterling"),\$R2,0)

=IF(AND(\$V2="SSS",\$W2="Sterling"),\$R2,0)

The problem was the "" at the end and not a 0
0

LVL 23

Assisted Solution

Ejgil Hedegaard earned 1000 total points
ID: 40481901
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

LVL 33

Expert Comment

ID: 40482625
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

LVL 85

Expert Comment

ID: 40482667
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

Author Closing Comment

ID: 40483723
Good stuff, thanks guys.   I really appreciate it.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month7 days, 21 hours left to enroll