Share this content
15

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

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

Didn't find your answer?

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!

Replies (15)

Please login or register to join the discussion.

avatar
By commonesnes
15th Jan 2015 17:42

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

Any ideas??

Thanks (0)
RLI
By lionofludesch
15th Jan 2015 17:54

Impatient or what ?

A reminder after a minute ????

Could be a currency thing.

Try entering in sterling. 

Thanks (1)
Replying to lionofludesch:
avatar
By commonesnes
15th Jan 2015 18:20

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!

Thanks (0)
avatar
By Sherman Holter
15th Jan 2015 17:58

Formatting

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

Thanks (0)
By Onion4Sage
15th Jan 2015 19:00

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

Ian

Ian Brown FCA
Onion Reporting Software Ltd

www.onionrs.co.uk

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

Thanks (0)
Replying to Accountant A:
avatar
By commonesnes
17th Jan 2015 21:00

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!

Thanks (0)
avatar
By JPMLondon
16th Jan 2015 15:28

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!

Thanks (0)
Replying to Paul D Utherone:
avatar
By commonesnes
16th Jan 2015 19:25

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,

 

 

Thanks (0)
By Onion4Sage
16th Jan 2015 15:35

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?

 

Thanks (0)
Replying to CoffeeInspired:
avatar
By commonesnes
16th Jan 2015 22:12

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!  

Thanks (0)
avatar
By BroadheadAccountants
16th Jan 2015 15:41

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.

Thanks (0)
avatar
By Cantona1
16th Jan 2015 15:44

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.

Thanks (0)
By mydoghasfleas
16th Jan 2015 15:54

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

 

 

Thanks (0)
Replying to lionofludesch:
avatar
By commonesnes
16th Jan 2015 20:39

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

mydoghasfleas wrote:

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

 

 

 

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

Thanks (0)
avatar
By edhy
17th Jan 2015 05:56

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

 

Thanks (0)
Share this content

Related posts