Welcome Guest
You last visited December 4, 2016, 7:09 am
All times shown are
Eastern Time (GMT-5:00)

# Help with Millions verses Thousands formula

Topic closed. 7 replies. Last post 7 years ago by KnuckleHead.

 Page 1 of 1

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: June 6, 2009, 1:36 pm - IP Logged

Morning all,

Does anyone know if a formula can be written to look at a cell that contains say "\$25,600,000.00" or \$480,000.00", then the displayed answer would be either "25.6 M" or "480 K".

In other words, the cell has either thousands or millions in it, and I would like to take whatever is in that cell and shorten it to either a "M" for millions or a "K" for thousands. Can a formula look at a cell and tell the difference in what to display as the answer since each draw amount would change?

Thanks ahead of time for any interest and help.

The only DUMB question is the one question you DID NOT ask...

New Jersey
United States
Member #17843
June 28, 2005
49642 Posts
Offline
 Posted: June 6, 2009, 10:02 pm - IP Logged

Data in Cell A3

In Cell B3:
@IF(A3<1000000,@STRING(A3/1000,2)&"K",@STRING(A3/1000000,2)&"M")

A mind once stretched by a new idea never returns to its original dimensions!

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: June 7, 2009, 12:50 pm - IP Logged

Thank you  Raven62

I've been trying to figure that one out. Needless to say, I'm not computer literate.

I copied your formula into the cell with the correct "target cell" in it and got the answer with 2 decimals. Looking at it, I modified it from:

@IF(A3<1000000,@STRING(A3/1000,2)&"K",@STRING(A3/1000000,2)&"M")

TO:

@IF(A3<1000000,@STRING(Target Cell/1000,0)&" K",@STRING(Target Cell/1000000,0)&" M")

Works great...Thank you!

Now with that figured out, is there a way to force the answer to become a number instead of a formula?

I have a row that contains "recent" draw info. That's what the above formula is doing, converting the winning amount from the "recent" row to the "history" row. If I insert a row, the formula will always refer to the original cell even if the contents change. That could be a problem. So, is there a way to "freeze" the answer, so that it doesn't recalculate based on new info being entered?

Thanks again.

The only DUMB question is the one question you DID NOT ask...

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: June 8, 2009, 9:55 am - IP Logged

Morning  Raven62,

I made a mistake with the formula,

@IF(A3<1000000,@STRING(Target Cell/1000,0)&" K",@STRING(Target Cell/1000000,0)&" M")

@IF(Target Cell<1000000,@STRING(Target Cell/1000,0)&" K",@STRING(Target Cell/1000000,0)&" M")

Where "Target Cell" is the cell you want to check...

The above formula works if you start off with "\$15,000,000.00" The answer is "15 M". But if you start off with "\$15,200,000.00", the answer is still "15 M". The correct answer should be "15.2 M".

I've tried including "Target Cell/100000" and "Target Cell/1000". Neither of those work either. I even tried "dividing", that also didn't work.

The side of the formula that calculates the "K" section works properly.

Also, I did figure out how to copy the "cell formula" and "paste the answer into another cell as a "value" instead of a formula.

Please, what am I doing wrong? And, Thank you.

The only DUMB question is the one question you DID NOT ask...

United States
Member #41846
June 23, 2006
458 Posts
Offline
 Posted: June 8, 2009, 12:35 pm - IP Logged

sounds as if you have formated the cell to have no decimals.  with the target cell highlited check format cells( exact method varies with what version of exce you are usingl).  set to the number of decimal places you want.

p8

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: June 9, 2009, 7:23 am - IP Logged

Morning  phileight,

My spreadsheet program is Lotus123, the cell with the formula is formated to "Gereral", with no decimal places.

As I manually enter the contents ("15 M" or "15.2 M"), no problem. The formula above calculates correctly if the "target cell" has "\$15,000,000.00" or "\$480,000.00" in it. The "target cell" is formated as "US Dollars".

Basicly, I'm inserting a new row into the "history" section and taking the contents of the "current winning" amount section and placing it in the "winning history" section.

I was attempting to "shorten" the current winning amount to make it easier to read (without all the zeros) in the "history" section. As you can see above, with Raven62's assistance, I can now set the shortened amount into it just by copying the data instead of having to manually enter it. I also figured out how to set the "answer" from a formula to a "number" so that it doesn't keep "tracking the "new" amount.

The problem is that the formula as written does not take into consideration the "hundred thousand" section in the "millions" side of the formula for the amount "\$15,200,000.00". It only calculates the "millions" section. Plus I would like it to place the "." in the proper ("15.2 M") place.

Not being computer literate, I haven't been able to figure out how to do that. I have figured out how to place the " " (space) into the formula so that the answer is properly set for the "M" or "K".

I know, for people that write formulas all the time, it's a "no biggie", but for someone like me, it's driving me nutts.

Thanks for the interest though, any suggestions?

The only DUMB question is the one question you DID NOT ask...

New Jersey
United States
Member #17843
June 28, 2005
49642 Posts
Offline
 Posted: June 9, 2009, 10:45 am - IP Logged

Morning  Raven62,

I made a mistake with the formula,

@IF(A3<1000000,@STRING(Target Cell/1000,0)&" K",@STRING(Target Cell/1000000,0)&" M")

@IF(Target Cell<1000000,@STRING(Target Cell/1000,0)&" K",@STRING(Target Cell/1000000,0)&" M")

Where "Target Cell" is the cell you want to check...

The above formula works if you start off with "\$15,000,000.00" The answer is "15 M". But if you start off with "\$15,200,000.00", the answer is still "15 M". The correct answer should be "15.2 M".

I've tried including "Target Cell/100000" and "Target Cell/1000". Neither of those work either. I even tried "dividing", that also didn't work.

The side of the formula that calculates the "K" section works properly.

Also, I did figure out how to copy the "cell formula" and "paste the answer into another cell as a "value" instead of a formula.

Please, what am I doing wrong? And, Thank you.

The above formula works if you start off with "\$15,000,000.00" The answer is "15 M". But if you start off with "\$15,200,000.00", the answer is still "15 M". The correct answer should be "15.2 M".

@IF(Target Cell<1000000,@STRING(Target Cell/1000,0)&" K",@STRING(Target Cell/1000000,1)&" M")

Instead of mixing thousands and millions you may want to consider using only one:

@STRING(Target Cell/1000000,1)&" M"

15,200,000 = 15.2 M

200,000 = .2 M

Just a thought!

A mind once stretched by a new idea never returns to its original dimensions!

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: June 9, 2009, 2:36 pm - IP Logged

Afternoon Raven62

By changing the "0" to "1". That did the trick.

Thank you.

The only DUMB question is the one question you DID NOT ask...

 Page 1 of 1