Question Excel column formula inexplicably skips a cell

[dareys]

Greetings,

As you will see in the below spreadsheet I havve a formula for column K (which is the same as column J) that does not calculate correctly.

Column J adds up fine, column K does not...

I would appreciate some help with this.

Thank you.

Jean-Pierre

 

[zebrax2]

Cell K3 is probably set as text instead of number.

 

[OlyAR15]

Same thing is happening in I3 as well. Usually Excel will warn you that the cell is formatted as text when you input numbers, though.

 

[OlyAR15]

There is a bug in Excel, at least up to Excel 2013 which is what I used to test it, that you cannot convert text to number if it already contains a number. I'm guessing that is what happened here.

Here is MS's workaround: https://support.microsoft.com/en-us...a-number-1791c3e1-c2b0-4e5e-6bd8-b100f291e4f4

By far the easiest workaround, since you only have two cells affected, is make sure those cells are formatted as number (or currency), then delete and retype the numbers into the cells.

 

[dareys]

Gentlemen,

Thank you for your feedback but even for an Excel neophyte, I know the the column format cannot be TEXT.

As a matter of fact I have changed the column format to currency N times...

Jean-Pierre

 

[OlyAR15]

Did you try deleting the contents of those two cells and re-enter the numbers? Because K3 in particular is not formatted properly. Note that it is not formatting to 2 decimal places.

 

[bba-tcg]

Gentlemen,

Thank you for your feedback but even for an Excel neophyte, I know the the column format cannot be TEXT.

As a matter of fact I have changed the column format to currency N times...

Jean-Pierre

Perhaps. But cell k3 is not formatted as currency, which is obvious from looking at contents of the cell. Even though you say you've formatted the column, format that cell specifically and see what happens.

 

[dareys]

Perhaps. But cell k3 is not formatted as currency, which is obvious from looking at contents of the cell. Even though you say you've formatted the column, format that cell specifically and see what happens.

Hello, thank you very much for the reply...

Again, I formatted the column as currency, two decimals, prefixed with a $ ...

In the past I evne delete the entire column, re-added it, re-formatted it and re-populate.

Same result...

Thank you.

Jean-Pierre

 

[mpo]

Try multiplying everything by 1. Quick way is to enter 1 into A10 and copy. Select B3:N5. Right-click the selection, then Paste Special, then multiply.

That may help force a numeric format for all cells.

 

[OlyAR15]

Probably best if you just start over. Format the cells first before entering any data.

Just to be sure: are you typing in the data, or copy/pasting from some other source?

 

[dareys]

Probably best if you just start over. Format the cells first before entering any data.

Just to be sure: are you typing in the data, or copy/pasting from some other source?

Greetings,

I clearly remember pasting cell K3 from my calculator...

Once I entered the data manually the formula worked fine.

Many thanks for your help.

Regards,

Jean-Pierre

 

[OlyAR15]

Glad it worked. Don't forget to fix cell I3 too.

 

[OlyAR15]

In Excel, at least in Excel 2013, which I used for testing, there is a bug: you cannot convert text to a number if it already contains a number.

What is the point of this post, especially since the problem had already been solved and I had already pointed out this bug in post #4?

There is a bug in Excel, at least up to Excel 2013 which is what I used to test it, that you cannot convert text to number if it already contains a number.