Data integrity - formatting cells

[falcios]

Excel 2010

In Excel, I have 3 columns:
Total price
Sub total
Tax

All the row calculations were correct but were not adding up in the column. I formatted the cells to make sure they were in currency format and still did not get the right sum. I copied the numbers to a blank worksheet and got the same answer.
I found a problem cell and reentered it manually and then it worked.

How can I ensure that the cells are formatted properly and all are included in the sum? If I didn't have the other columns to compare I would take the total price sum at face value.

Thanks in advance.

 

[DSCfromCFA]

G'day falcios,

What was the problem with the 'problem cell'?

You imply it was a formatting issue, but tou also stated that you had formatted the cells to ensure they were correct. What did or didn't happen when you formatted the cells?

shane

 

[falcios]

Initially I thought it was a formatting issue so I formatted using number currency and it didn't work.

I totaled the subtotal and tax columns and subtracted from the total price and it gave me a dollar amount that I could trace to a single cell. I just retyped the number again and it worked. That's my question, I don't understand what was the problem with that cell and why wasn't it included in the total sum.

 

[DSCfromCFA]

Thanks falcios,

I am far from being adept at excel, but one of the things that springs to mind is that the cell contained some character that excel considered text or a non-number. If you still have the copy in the other worksheet, you may be able to establish if this is the case using some of the functions available, LEN, ISNUMBER, etc.

A fix that should work for you is to apply Data Validation to the currency columns to only accept Decimal input.

Regards

shane

 

[falcios]

Thanks Shane

ISNUMBER formula did work. Also, I tried Data Validation to allow decimal but it did not show any errors.
Do you have any suggestions what other parameters I can use for Data Validation. All the cells are in number format, except 3 which are in text.

Thanks in advance.

 

[DSCfromCFA]

G'day Falcios,

Sorry for the delayed response. I don't understand your comment about Data Validation not showing any errors.

Does that mean that there were errors and they didn't show up? If so there is a not so obvious option - Apply Data Validation to the cells as per normal (any data not in range will not be identified), then exit and then click on the little pull down arrow undeneath the DV icon and select Circle Invalid Data option to have any data that is outside of the parameters identified.

Cheers

shane