Cell formatting 2

[bnsSteve]

Hello,

I'm trying to format a cell that is referencing another cell in another sheet, to round to the nearest 1,000 and add 'M' to the end of the number.

For example, B6 in my primary sheet is referencing B6 in my Data sheet (=Data!6). It currently shows a currency amount, ex; $387,691. What I would like for it to show is $388M (rounding to the nearest 1,000 truncating the rest and adding M to the end while still referencing the other cell.

Hope this is possible, thanks for the help!

Running Crystal 11 - 14.0.2.364

 

[SkipVought]

[tt]
=ROUNDUP(Data!B6/1000,0)&"M"
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bnsSteve]

Thanks Skip for the fast reply that was easy!

Running Crystal 11 - 14.0.2.364

 

[bnsSteve]

Sorry one more question, is it possible to add commas for values that in millions? i.e, $2,178M and not $2178M?

Thanks again.

Running Crystal 11 - 14.0.2.364

 

[SkipVought]

[tt]
=TEXT(ROUNDUP(data!B6/1000,0),"#,000")&"M"
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[1726]

Try a Custom Format code of the form:

$#,"M";-$#,"M";$0,"M

 

[bnsSteve]

Thanks Skip works great!

and thanks 1726 for the reply as well. Your solution works great as well except the comma wouldn't take for values with > 4 digits.



Running Crystal 11 - 14.0.2.364

 

[SkipVought]

Steve: is that Burlington Northern & SantaFe?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gruuuu]

I would still recommend a custom formatting

[tt]
#,###,"M";-#,###,"M";"-";@
[/tt]

takes care of the arbitrary length, and still lets you perform calculations on the number.

You mentioned rounding your numbers up to the nearest thousands place. Do you really mean always? Even if it's 24,001 you want to see 25M?

If that's the case, you should still use that custom formatting, and then in your formula you would key in =Data!B6+499, but at that point, you shouldn't be adding anything together since the figure will be way off.

I suspect you don't actually mean to always round up, but then again I don't know.

 

[bnsSteve]

Skip: No sorry it doesn't stand for Burlington Northern.

Gruuuu: Thanks for the reply, I think this would be the better approach for what I am looking for.

Cheers!

Running Crystal 11 - 14.0.2.364

 

[bnsSteve]

Sorry I've run into a problem when the numbers are being rounded. Is there a way to sum up the values from the format?

I.e,
RowA= $923M
RowB= $522M
Total= $1,444M

The true value of the column sum is $1,444 but since the format is rounding the numbers the totals is not correct. Would need for the totals to be summing after its been rounded. Trying to find a way that it would round up but not values that are "24,001 to 25M"

Not sure if this is possible considering I'm adding a string to the end of the value.

Thanks again for all the help, much appreciated.

Running Crystal 11 - 14.0.2.364

 

[SkipVought]

You need to state the full scope of the requirement UP FRONT.

Otherwise, you may get impractical solutions, like what happened here!

So do you want to add the ROUNDED values or the ORIGINAL values? This is what Gruuuu's post referred to.

Please state the entire requirement clearly, concisely and completely.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bnsSteve]

Hi Skip & Gruuuu,

Sorry for the late response - Yeah I should of stated it better in the original post I did not foresee the problems that could arise from not doing so.

I'll try to explain it better below - I currently have two sheets, 'Graphs' which is the main sheet that I have all the final consolidated values and graphs on which I print out, second is the Data sheet where all the calculations and monthly data are entered into.

I've created a table in the data sheet that sums up different categories within the same sheet. i.e,
Totals for April
Deposit - 321,436
Credit - 594,380
Total - 915,816

In my Graphs sheet, I reference this table and have used the rounding solutions that were stated above. Which shows
Deposit - $321M
Credit - $594M
Total - $916M -> where my problem arises.
So to clarify it more directly I hope, I would like the Total to sum up the rounded values in my Graph's sheet. Otherwise I run into the problem of the rounded values not equaling.

Thanks again for all the help, greatly appreciated.

Running Crystal 11 - 14.0.2.364

 

[Deniall]

If you want "the total to sum up the rounded values", why not simply use a formula that does just that? Don't create the rounded total by rounding the total of the unrounded numbers. Create it by totalling the rounded numbers.

Or have I missed something here?

This is a problem that frequently arises with rounded values. If you have N values where N is reasonably large, and you round each of them to the nearest R (where R is 1000 in your example), then on average the difference between the total of your original values and the total of your rounded values will be about
0.2 x Sqrt(N) x R

 

[bnsSteve]

Hi Deniall, thanks for the reply.

Is there a way to sum up the face value of cells that are referencing another cell? Since the rounded cells have a formula in them referencing the data sheet, suming up the values just revert to suming up the data sheet so I still run into the problem where the deposit and credit rounded values are rounding down but when combined the totals cell gets rounded up.

If I could find a way for it to sum up the rounded cells that are referencing another cell that would work nicely. I could just do it all manually but would prefer that when I enter in information into my data sheet the main graphs sheet will populate automatically for me.

I hope I'm missing something simple.

Thanks again.

Running Crystal 11 - 14.0.2.364

 

[Deniall]

You can embed a ROUND function within a SUM function providing you enter it as an Array Function. (This is done with <crtl-shift-enter> rather than just <enter>.)

I would upload a small example, but don't want to have to enroll myself in MediaFire.

 

[bnsSteve]

Thanks Denaill! I'll try that out.

Running Crystal 11 - 14.0.2.364