Help with Millions verses Thousands formula

Reply #7

KnuckleHead — Tue, 09 Jun 2009 18:36:52 GMT

Afternoon Raven62

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

Thank you.

Reply #6

Raven62 — Tue, 09 Jun 2009 14:45:48 GMT

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

Reply #5

KnuckleHead — Tue, 09 Jun 2009 11:23:38 GMT

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 his... [ More ]

Reply #4

phileight — Mon, 08 Jun 2009 16:35:30 GMT

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

Reply #3

KnuckleHead — Mon, 08 Jun 2009 13:55:33 GMT

Morning Raven62,

I made a mistake with the formula,

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

It should read;

@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...

Sorry about that.

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 an... [ More ]

Reply #2

KnuckleHead — Sun, 07 Jun 2009 16:50:16 GMT

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 forc... [ More ]

Reply #1

Raven62 — Sun, 07 Jun 2009 02:02:05 GMT

Data in Cell A3

In Cell B3:

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

Help with Millions verses Thousands formula

KnuckleHead — Sat, 06 Jun 2009 17:36:03 GMT

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?

Tha... [ More ]