I have a spread sheet with multiple tabs. I am trying to add 3 cells in the same tab and I get only $0.00. This is happening in multiple cells, not just one. Any ideas on how to correct the problem are much appreciated!

15th Jan 2015

# Excel Posting $0.00 when I try to add cells together. T

Excel Posting $0.00 when I try to add cells...

Share this content

## Replies (15)

## Please login or register to join the discussion.

Excel Posting $0.00 when I try to add cells together. T

Any ideas??

Impatient or what ?

A reminder after a minute ????

Could be a currency thing.

Try entering in sterling.

Thank you I will try using Sterling. All the other cells are in $ and most work properly . The reminder was a test. I receive a black comment email form. I sent it to myself as a test. Sorry!

Formatting

Maybe the cells all formatted as text. Right-click the cells, select format, number.

Enter only numbers

HI commonesnes,

Might you be entering a $ in the three cells? Don't. Let the formatting of the cells take care of putting in the currency symbol. Only put numbers in the three cells.

I hope this helps.

Regards

IanIan Brown FCA

Onion Reporting Software Ltd

www.onionrs.co.uk

Sage accounts in Excel to go. No set-up necessary.Hi Ian,

I am not entering $ signs but they could be in the cells imported from Edgar. I'll check. Since I format the spreadsheet into currency before I download into it, the $ dollar signs could be in the downloaded data.

Thanks!

It could be because of binary v decimal systems probably. You can google it if you feel geeky. Short version is that excel might show you a decimal number, but 'inside' it's all binary which means that sometimes numbers are not completely accurate and there might be an error many many digits down.

Solution in this case is basically to format all cells using round formula to two digits...

Hope this helps!

Getting 000s

You may be on track here. The file is an xlsb. I am no Excel expert but isn't that a binary file. If so, since we are rounding to thousand and the data had 3 decimal points this could be an issue. By rounding to two digits do you mean two digits after the decimal point or two digits in front of the decimal point. This issue may be arising because its a valuation engagement and sources of financial downloads for public company comparable data are not consistent, i.e., some are in millions followed by two or three decimal places and others are in thousands.

Does anyone know how to convert the entire spread sheet to thousands without having to one by one multiplying every cell in the spread sheets utilizing millions but 1000?

Thanks,

Contents of formula bar

Hi commonesnes,

Can you reveal the contents of the three cells as they appear in the formula bar for us all?

Hi Onion4Sage.

The Company is a exec search firm:

Column is restating income statement for 2011 to reflect eliminations of non-operating expenses included in expense in order to determine NOPAT. The information from EDGAR was downloaded and added to tabs in the spreadsheet: one tab each for for KFY Bal Sheet, Income Statement and Cash Flow Statement. Cells in those tabs were then referenced in the Adjusted Income Statement, Balance Sheet and Cash Flow Statements from Operations. The references in the cell contents below are references to those tabs which where moved into the spreadsheet from EDGAR. Below in one example of when I tried to calculate Total Operating Costs the cell showed 0.00. Thanks!

Cell # Cell Contents______________________Given: Net Revenue, i.e., net of reimbursed engagement costs. C11 =1/1000*'KFY-Anl_4-14-12 Inc.State (2)'!H3

EBITDA before Adjustments Different Tab = 'KFY-Anl_4-14-12 Inc.State (2)'I13

Comp & Benefit Exp before adjustments C16 534.186

Unreimbursed Cost of Engagements C21 ='KFY-Anl_4-14-12 Inc.State (2)'!H9/1000

Compute: COGS = Comp & Benefits Exp + Unreimbursed Engagement Costs C24 =C16+C21

SGA (was not itemized) = EBITDA before Adjustments - COGS C30 =C27-'KFY-Anl_4-14-12 Inc.State (2)'!I13

Adjustments: Eliminate Non-operating Deferred Comp Costs C35 =Sheet9!J83

Eliminate Implied Interest On Operating Lease (Noteentire operating lease pmt. C34 =-Sheet8!C34

included in SG&A. Implied interest is a financing v. operating cost.

Compute Total Operating Costs, i.e., After Adjustments, C37 =C24+C30+C34

^This is where is get 0.00 when I calculate!^Compute EBITDA after Adjustments, i.e., Net Revenue - Total Operating Costs. C39 =C11-C37

Thanks!

Auto-calculate or manual calculations

Interesting; try:

Circular references,

Re-formatting,

Ensuring other cells work and that the s/sheet isn't set to manually calculate.

Are the data imported from other systems such Outlook, database or a web in to excel? If so, they might appear "Numbers" to eyes as but not to excel. Check by using the formula =Value(A1) ,where A1 is the cell address.

Merged and unmerged cells

If the total cell already existed it could be that you have merged cells

If cells are merged and the total cell is not, you could be adding 3 nil entries, hence the answer. E.G. You had sum(B1:B3) in B4 but since writing that have merged A1 with B1, A2 with B2 and A3 with B3. The value in B4 is because those merged cells are registered in column A.

Is the sheet set for auto calculation?

Of course if it is a spread sheet rather than a spreadsheet, you might want to change the linen

Merged & Unmerged cells - Get 000s if Add Cell in same Column

I think a spread sheet and a little more sleep would also help right now!

Meanwhile, I will check for merged cells. I usually don't merge sells except on the far left or top of a spread sheet because of these types of complications.

Thanks

Are C11 and C37 both showing result (numbers) and same for other intermediate result.

Since you are getting numbers from other sheets / files and dividing these by 1000, check that final result is large enough to appear in 2 digits after decimal. To check that increase decimal places (format) in your result cell.

Check source cells, there might be zero at some critical cell making the result zero.

Finally do check that reference is not to merged cells.

As you are using +, - operators, text looking like number will work, (data type coercion), =SUM ignores texts.

Binary or XML file format should not make any difference in this case. By the way Excel keeps accuracy up to 15 digits after decimal.

Regards

Zubair Edhy