Why doesn't adding numbers work in Excel?

[GamerGry123]

So I was looking on the internet how to do it and it was that there was a space before the SUM function or that the cells must be formatted as general or I also did numbers and it doesn't work. Does anyone know what's going on?

And how to make the numbers from the BUY Excel add up automatically and how to make the same from the SELL cell, because when manually selecting something can be missed.

[Skipple]

Cells are not formatted as numbers. They are formatted as text.

[GamerGry123]

1 minute ago, Skipple said:

Komórki nie są formatowane jako liczby. Są one sformatowane jako tekst.

Why? Doesn't understand. It says on the internet that they must be formatted as numbers.

[Skipple]

4 minutes ago, GamerGry123 said:

It says on the internet that they must be formatted as numbers.

That's correct. They are currently formatted as text, which is why you are getting an output from SUM of 0, as there is nothing to add. 

You can tell they are currently formatted as text because the numbers are left-justified, rather than right-justified.

The cells needs to be converted to numbers.

 

https://support.microsoft.com/en-au/office/convert-numbers-stored-as-text-to-numbers-40105f2a-fe79-4477-a171-c5bad0f0a885

[Skiiwee29]

<-- moved to programs, apps, and websites -->

 

Please ensure you're selecting the most appropriate subforum for new topics and not just creating in general discussion.

[PDifolco]

33 minutes ago, GamerGry123 said:

Why? Doesn't understand. It says on the internet that they must be formatted as numbers.

What's your decimal separator in Windows/Excel ? If it's a comma "," it wont take 250.00 (with a dot) as a number but as text

I've got this issue at work with a mix of French and US files and Windows setups

[GamerGry123]

13 hours ago, Skiiwee29 said:

<-- przeniesiono do programów, aplikacji i stron internetowych -->

 

Upewnij się, że wybierasz najodpowiedniejsze podforum do nowych tematów, a nie tylko do tworzenia ogólnej dyskusji.

I searched there is no other category for this.

[GamerGry123]

13 hours ago, Skipple said:

To jest poprawne. Obecnie są sformatowane jako tekst, dlatego otrzymujesz wynik z SUM równy 0, ponieważ nie ma nic do dodania. 

Możesz stwierdzić, że są obecnie sformatowane jako tekst, ponieważ liczby są wyrównane do lewej, a nie do prawej.

Komórki należy przekonwertować na liczby.

 

https://support.microsoft.com/en-au/office/convert-numbers-stored-as-text-to-numbers-40105f2a-fe79-4477-a171-c5bad0f0a885

I just did it on lcizb but it still doesn't work. I am sending you a file where you can see it for yourself. I don't know why it doesn't work anymore, I rubbed all the things that were written on the internet about it.

 

Same with Google Sheets. I tried it on the browser version and on other programs similar to Excel and I don't know anymore.

13 hours ago, PDifolco said:

Jaki jest separator dziesiętny w systemie Windows/Excel? Jeśli to przecinek "," nie zajmie 250,00 (z kropką) jako liczby, ale jako tekst

Mam ten problem w pracy z mieszanką francuskich i amerykańskich plików oraz konfiguracji systemu Windows

I don't quite understand. Does it have to be a dot instead of a comma?

Excel.xlsx

[PDifolco]

3 hours ago, GamerGry123 said:

I just did it on lcizb but it still doesn't work. I am sending you a file where you can see it for yourself. I don't know why it doesn't work anymore, I rubbed all the things that were written on the internet about it.

 

Same with Google Sheets. I tried it on the browser version and on other programs similar to Excel and I don't know anymore.

I don't quite understand. Does it have to be a dot instead of a comma?

Excel.xlsx 9.22 kB · 0 downloads

Yes you need to have dot as separator so it's understood as a number, else it's like if you had 250§00

[LogicalDrm]

On 7/16/2023 at 3:13 PM, GamerGry123 said:

I just did it on lcizb but it still doesn't work. I am sending you a file where you can see it for yourself. I don't know why it doesn't work anymore, I rubbed all the things that were written on the internet about it.

 

Same with Google Sheets. I tried it on the browser version and on other programs similar to Excel and I don't know anymore.

I don't quite understand. Does it have to be a dot instead of a comma?Excel.xlsx 9.22 kB · 0 downloads

The main problem is that your system settings use comma as decimal separator. You can change it if you use more US/UK based softwares or data. For just Excel or Google Sheets, I would Replace any dots in number cells.

 

If you go on with the change, there are three places to do it. One is system wide and will effect on how numpad decimal separator works too. On Excel, you can change per software settings. For Sheets, its using locale, so you would need to select US/UK for it to work properly.

[GamerGry123]

14 hours ago, LogicalDrm said:

Głównym problemem jest to, że ustawienia systemu używają przecinka jako separatora dziesiętnego. Możesz to zmienić, jeśli używasz więcej oprogramowania lub danych z USA/Wielkiej Brytanii. Tylko w przypadku programu Excel lub Arkuszy Google zamieniłbym wszelkie kropki w komórkach liczbowych.

 

Jeśli zdecydujesz się na zmianę, możesz to zrobić w trzech miejscach. Jeden dotyczy całego systemu i wpłynie również na sposób działania separatora dziesiętnego na klawiaturze numerycznej. W programie Excel możesz zmienić ustawienia oprogramowania. W przypadku Arkuszy używane są ustawienia regionalne, więc aby działały poprawnie, musisz wybrać USA/Wielką Brytanię.

You need to change the language to the USA or England and then the numbers are written with a comma, it will work well?

 

Is it possible to make Excel automatically convert all commas to dots in numbers? How and where is it done?

[LogicalDrm]

1 hour ago, GamerGry123 said:

You need to change the language to the USA or England and then the numbers are written with a comma, it will work well?

Not language, but regional settings. It's from same menu, but you can pick them individually. Language as one, numeric options as another and date/time as third.

 

1 hour ago, GamerGry123 said:

Is it possible to make Excel automatically convert all commas to dots in numbers? How and where is it done?

I'm not sure, and I would prefer less such automation from Excel. It's quite easy and fast to select column and find-replace. I'd rather have dots in my control rather than auto-change them, usually to date.

[Skipple]

7 minutes ago, LogicalDrm said:

I'd rather have dots in my control rather than auto-change them, usually to date.

Excel has a knack of changing numbers to dates when it shouldn't and not changing to date when I do want it to.

[GamerGry123]

24 minutes ago, LogicalDrm said:

Nie język, ale ustawienia regionalne. Pochodzi z tego samego menu, ale możesz wybrać je indywidualnie. Język jako jedno, opcje numeryczne jako drugie, a data/godzina jako trzecie.

 

Nie jestem pewien, a wolałbym mniej takiej automatyzacji z Excela. Wybranie kolumny i znalezienie-zastąpienie jest dość łatwe i szybkie. Wolałbym mieć kropki pod kontrolą, niż zmieniać je automatycznie, zwykle na bieżąco.

 

21 minutes ago, Skipple said:

Excel ma talent do zmieniania liczb na daty, kiedy nie powinien, i nie zmieniania dat, kiedy chcę.

Where does it replace it? How is the number written with a comma is it a date?

I will send a video of what it looks like. In the new file, he writes a number into the cells and gives them with the SUM function and it works. But I have a ready extract file and it doesn't work on it how to make it work. I changed to English Excel, the cell is set to number digit.

36 minutes ago, LogicalDrm said:

Nie język, ale ustawienia regionalne. Pochodzi z tego samego menu, ale możesz wybrać je indywidualnie. Język jako jedno, opcje numeryczne jako drugie, a data/godzina jako trzecie.

 

Nie jestem pewien, a wolałbym mniej takiej automatyzacji z Excela. Wybranie kolumny i znalezienie-zastąpienie jest dość łatwe i szybkie. Wolałbym mieć kropki pod kontrolą, niż zmieniać je automatycznie, zwykle na bieżąco.

How is the region changing? You can only change the language and I changed it to American English.

I sent a link to the video of what it looks like.

 

LINK: https://photos.app.goo.gl/5U56FWdee682QtLx9

[tikker]

If you want to change them, in Office 2016 (probably similar in newer/other versions) you go to  File > Options > Advanced. Then under find and untick "Use system separators" and set the decimal and thousands separators to what you like them to be.

[LogicalDrm]

8 hours ago, GamerGry123 said:

Where does it replace it? How is the number written with a comma is it a date?

I will send a video of what it looks like. In the new file, he writes a number into the cells and gives them with the SUM function and it works. But I have a ready extract file and it doesn't work on it how to make it work. I changed to English Excel, the cell is set to number digit.

Find and Replace feature is activated with Ctrl+F. From that, go to Replace tab (you are using localized version), so its something equivalent. Here:

Selecting column or range is better as your Replace will only effect on selection by default. So if you would have dates, IPs or emails, those wouldn't be messed as result.

[GamerGry123]

On 7/18/2023 at 6:46 PM, tikker said:

If you want to change them, in Office 2016 (probably similar in newer/other versions) you go to  File > Options > Advanced. Then under find and untick "Use system separators" and set the decimal and thousands separators to what you like them to be.

How to set it? I had a comma set there

[GamerGry123]

On 18.07.2023 at 22:20, LogicalDrm said:

Funkcja Znajdź i zamień jest aktywowana za pomocą Ctrl + F. Następnie przejdź do zakładki Zastąp (używasz zlokalizowanej wersji), więc jest to coś równoważnego. Tutaj:

 

Wybranie kolumny lub zakresu jest lepsze, ponieważ opcja Zamień domyślnie wpłynie tylko na zaznaczenie. Więc gdybyś miał daty, adresy IP lub e-maile, nie zostałyby one pomieszane w wyniku.

I changed it to a comma and it still doesn't add up.

[LogicalDrm]

42 minutes ago, GamerGry123 said:

I changed it to a comma and it still doesn't add up.

I'm guessing you have manually changed how cells are formated. Changing that manually to number should fix it.