LinkBack URL
About LinkBacks
I want numbers stored in column display like 8 Crore or 7 lakh or 20 thousand. For example :-
SR Basic data Data that i want 1 625325 6 lakh 2 21456325 2 Crore 3 3568569 35 lakh 4 1020 1 thousand 5 456123456 45 crore
I want numbers stored in column display like 8 Crore or 7 lakh or 20 thousand. For example :-
SR Basic data Data that i want 1 625325 6 lakh 2 21456325 2 Crore 3 3568569 35 lakh 4 1020 1 thousand 5 456123456 45 crore
In C2 copied down:
=IF(B2>10000000,INT(B2/10000000),IF(B2>100000,INT(B2/100000),IF(B2>1000,INT(B2/1000),B2)))&" "&LOOKUP(B2,{0,1000,100000,10000000},{"","thousand","lakh","Crore"})
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
thanks for quick replyu solved my problem
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
helo pls i want some extension in my query like:-
135504210 should be shown as 13.55 crore
29117641 should be shown as 2.91 crore
8256824 should be shown as 82.57 lakh
8306440 should be shown as 83.06 lakh
Change the formula to this:
=IF(B2>10000000,ROUND(B2/10000000,2),IF(B2>100000,ROUND(B2/100000,2),IF(B2>1000,ROUND(B2/1000,2),B2)))&" "&LOOKUP(B2,{0,1000,100000,10000000},{"","thousand","lakh","Crore"})
or this, depending on what you want:
=IF(B2>10000000,ROUND(B2/10000000,2),IF(B2>100000,ROUND(B2/100000,2),IF(B2>1000,INT(B2/1000),B2)))&" "&LOOKUP(B2,{0,1000,100000,10000000},{"","thousand","lakh","Crore"})
thank u so much very pleased to see ur quick response
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Using LOOKUP for both parts:
=ROUND(B2/LOOKUP(B2,{0,1000,100000,10000000},{1,1000,100000,10000000}),2)&LOOKUP(B2,{0,1000,100000,10000000},{""," thousand"," lakh"," Crore"})
That's fine as long as the OP DOES want the thousands to round - at the moment, that's not entirely clear, hence my two options.
Hope it looks better
Please Login or Register to view this content.
Quang PT
Thanks for the rep.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks