Question Excel column formula inexplicably skips a cell

dareys

Junior Member
Oct 27, 2021
4
0
6
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

1635347786521.png
 

OlyAR15

Senior member
Oct 23, 2014
710
99
91
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

Senior member
Oct 23, 2014
710
99
91
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/topic/you-cannot-convert-text-to-number-when-the-cell-contains-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

Junior Member
Oct 27, 2021
4
0
6
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

Senior member
Oct 23, 2014
710
99
91
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.
 

Sick Willie

Senior member
Apr 8, 2010
204
23
81
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

Junior Member
Oct 27, 2021
4
0
6
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...:eek:

Thank you.

Jean-Pierre
 

Attachments

mpo

Senior member
Jan 8, 2010
423
32
91
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

Senior member
Oct 23, 2014
710
99
91
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

Junior Member
Oct 27, 2021
4
0
6
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
 
Thread starter Similar threads Forum Replies Date
pete6032 Windows 2

ASK THE COMMUNITY