Solved

File Formatting is Preventing Correct Formula Calculation

Posted on 2014-12-04
5
68 Views
Last Modified: 2014-12-05
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
Comment
Question by:thutchinson
5 Comments
 
LVL 37

Accepted Solution

by:
Neil Russell earned 250 total points
Comment Utility
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
 
LVL 21

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 250 total points
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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

by:Rory Archibald
Comment Utility
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

by:thutchinson
Comment Utility
Good stuff, thanks guys.   I really appreciate it.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
AutoFilter Delete not keeping Headers? 2 12
TT Text To Column Based On Criteria 3 18
Excel 6 18
Name Rotation 11 24
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now