excel calculation error

%3CLINGO-SUB%20id%3D%22lingo-sub-1439946%22%20slang%3D%22en-US%22%3Eexcel%20calculation%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1439946%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20been%20using%20excel%20for%20years%20and%20never%20encountered%20this%20problem%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20simple%20calculation%20that%20will%20just%20not%20work%3F%3C%2FP%3E%3CP%3EYou%20can%20see%20the%20numbers%20are%20correct%20and%20will%20calculate%20in%20a%20cell%20directly%20but%20when%20the%20calculation%20is%20done%20using%20an%20if%20statement%20it%20will%20not%20work.%3C%2FP%3E%3CP%3Ei%20have%20checked%20that%20the%20options%20box%20is%20ticked%20for%20automatic%20and%20number%20formats%20etc.%3C%2FP%3E%3CP%3EThe%20simple%20if%20statement%20is%26nbsp%3B%20%3Dif(a%26gt%3Bb%2C%20%22Y%22%2C%22N)%20or%20any%20thing%20similar%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1439946%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1440005%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20calculation%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1440005%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F688909%22%20target%3D%22_blank%22%3E%40kingdom42%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInstead%20of%20screenshot%20it's%20better%20to%20have%20sample%20file%2C%20otherwise%20we%20could%20discuss%20for%20a%20long%20while%20are%20separators%20comma%20or%20semicolons%20and%20other%26nbsp%3B%20variants.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1440104%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20calculation%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1440104%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F688909%22%20target%3D%22_blank%22%3E%40kingdom42%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20a%20list%20separator%20in%20Windows%20settings%3C%2FP%3E%0A%3CP%3EWin%2BR%20%3CSTRONG%3Econtrol%20international%3C%2FSTRONG%3E%20Advanced%20settings%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443633%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20calculation%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443633%22%20slang%3D%22en-US%22%3EThanks%3CBR%20%2F%3EI%20am%20going%20to%20add%20a%20new%20post%20showing%20another%20problem%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443662%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20calculation%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443662%22%20slang%3D%22en-US%22%3EI%20believe%20the%20problem%20may%20be%20connected%20with%20the%20fact%20that%20I%20exported%20a%20csv%20file%20from%20another%20program%20and%20then%20used%20the%20data%20on%20the%20sheet%20for%20some%20calculations%20Even%20after%20I%20saved%20the%20file%20as%20a%20workbook%20.xls%20the%20problem%20persists.%3CBR%20%2F%3EFor%20example%20two%20numbers%20%C2%A324%2C86%20and%20-%C2%A320%20wen%20added%20show%20as%20%C2%A34860.%3CBR%20%2F%3ENothing%20I%20do%20in%20the%20format%20options%20will%20change%20that%20and%20it%20defaults%20to%20%22Custom%22%20even%20after%20i%20select%20%22Currency%22%3CBR%20%2F%3EI%20have%20a%20screen%20shot%20which%20I%20will%20try%20to%20attach%20not%20sure%20if%20I%20can%20do%20that.%3CBR%20%2F%3E%3CBR%20%2F%3Eis%20it%20possible%20that%20using%20a%20CSV%20file%20could%20alter%20the%20excel%20settings%3F%3CBR%20%2F%3Eregards%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1440083%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20calculation%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1440083%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20just%20discovered%20that%20the%20separator%20needs%20to%20be%26nbsp%3B%20%3CSTRONG%3E%3B%3C%2FSTRONG%3E%26nbsp%3B%20instead%20of%20a%26nbsp%3B%20%26nbsp%3B%3CSTRONG%3E%2C%3C%2FSTRONG%3E%26nbsp%3B%20which%20i%20have%20always%20used%20before.%3C%2FP%3E%3CP%3EI%20assume%20there%20is%20some%20tick%20box%20in%20the%20options%20which%20has%20changed%20%2Chave%20to%20have%20a%20look%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2288%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2288%22%20height%3D%2225%22%3EIF(H2%26gt%3BI2%3B1%3B2)%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CSTRONG%3Eworks%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi

I have been using excel for years and never encountered this problem

 

A simple calculation that will just not work?

You can see the numbers are correct and will calculate in a cell directly but when the calculation is done using an if statement it will not work.

i have checked that the options box is ticked for automatic and number formats etc.

The simple if statement is  =if(a>b, "Y","N) or any thing similar?

5 Replies

@kingdom42 

Instead of screenshot it's better to have sample file, otherwise we could discuss for a long while are separators comma or semicolons and other  variants.

Highlighted

I have just discovered that the separator needs to be  ;  instead of a   ,  which i have always used before.

I assume there is some tick box in the options which has changed ,have to have a look

IF(H2>I2;1;2)

works

Highlighted

@kingdom42 

That's a list separator in Windows settings

Win+R control international Advanced settings

 

 

Highlighted
Thanks
I am going to add a new post showing another problem
Highlighted
I believe the problem may be connected with the fact that I exported a csv file from another program and then used the data on the sheet for some calculations Even after I saved the file as a workbook .xls the problem persists.
For example two numbers £24,86 and -£20 wen added show as £4860.
Nothing I do in the format options will change that and it defaults to "Custom" even after i select "Currency"
I have a screen shot which I will try to attach not sure if I can do that.

is it possible that using a CSV file could alter the excel settings?
regards