Excel Calculation

[rajeev]

Hi I am importing data to excel from other windows which is in the
following format:- 2,100 22,000 2,32,000 & so on When i do mathematical
calculation on 2,100 & 22,000 it is ok but when i work on 2,32,000 it
says #VALUE!i.e any number having more then two or more then two
commas. Why i am getting this effect.

Thanks in advance

Rajeev

 

[Gary's Student]

EXCEL will have a problem with 2,32,000. Is this number two million three
hundred twenty thousand or two million thirty thousand?

There are not enough digits between the commas.

 

[rajeev]

It is neither of the two but is two lacs thirty two thousand i.e.one
digit less then million.1million=10 Lacs.We in india generally uses
lacs figures instead of millions.If this fig. is 2320000 then it can be
called as two point three million & thirty two thousand.
I hope now u can understand the relation between millions & lacs & can
be able to solve my problem.

Thanks
Rajeev

 

[Mangesh Yadav]

Hi Rajeev,

formatting does not change the value of a number in excel. Just check if
excel is treating 2,32,000 as text, which you can simple come to know
visually if it right- or left-aligned. If it is left aligned then it is
text. Otherwise just check the cell with the function:
=ISNUMBER(A1)
It should return true for numbers and false for text.

BTW, what formula or operation are you trying to do on these numbers.

Mangesh

 

[rajeev]

Hi Mangesh,
I have checked it by using ISNUMBER(CELL NO.) & it says FALSE.
Actually i am impoting data from Tally7.2 Accounting Package & making
some mathematical Calculation such as addition,subtraction on it.I
tried by using format cell & then changing text to number but no
result.

Thanks
Rajeev

 

[Mangesh Yadav]

Hi Rajeev,

Just check some of the following things. See if there are any leading or
trailing blanks (spaces) with the number. You could also use trim(A1) to
remove the spaces at either ends of the number.
Also try to edit the number i.e. select the cell. Press F2 and Enter.
Let me know.

Mangesh

 

[rajeev]

Hi Mangesh,
I tried going to cell & pressing F2 & re type that number & got the
result but my limitation is the number of data which is to be changed
manually.There are about 500 numbers & changing each of them manually
is a very long process.
I think if i can remove commas from number then i may be able to get
the desired results.Is there any command in excel for removing commas.

Thanks for your taking interest in my query.

Rajeev

 

[Mangesh Yadav]

Hi Rajeev,

I will tell you an automatic process only, but i wanted to confirm. What I
wanted you to do is, go to the cell. Press F2, and then enter. Don't change
anything at all. If this process converts the text to number, the solution
to this is:
Select the entire column, go to Data > Text to columns. Press finish. This
should solve your problem if the above mentioned case happens.

To simply replace the comma, do find replace, but i dont think this would
solve your problem. And note that cells with number formats with commas,
will not be affected with this replace action.

Mangesh

 

[rajeev]

Hi Mangesh,
I have tried but no results.
If you can give your e-mail id so that i can send you an attachment & i
think this will be an easy way to find solution.

Thanks
Rajeev

 

[Mangesh Yadav]

Sure. no problem. Here's my email id:
(e-mail address removed)
and don't forget to remove 'REMOVETHIS' from the id.

Mangesh