Converting Y-Axis to show MB rather than just Bytes

[tourcd]

Hi,

I have an Excel sheet with two columns, DATETIME and BYTES.

I'd like to chart this but alter the Y-axis scale to show the values in MB or GB. This has to be done without altering the raw data or adding additional columns etc.

So an example row is:

05/05/2013 00:00 742436864

So in the above example the bytes value translates to 708MB. I can easily alter the number format by shifting the decimal place but dividing by 1000 is not the right answer as there's 1024 bytes in a megabyte.

Does anyone have any ideas on this?

Many thanks.

 

[SkipVought]

hi,

Just for clarification...

Bytes             Power
                  of 2 
            1,024 10   1 KB
        1,048,576 20   1 MB
    1,073,741,824 30   1 GB
1,099,511,627,776 40   1 TB

Just make a new column.

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

 

[tourcd]

Sorry I did say "This has to be done without altering the raw data or adding additional columns etc."

Done some more Googlin' but not having much luck.

 

[SkipVought]

Then how are your programming (VBA) skills?

Please also explain why "This has to be done without altering the raw data or adding additional columns". It's like saying, "I must be able to drive the freeway blindfolded." Sure you could do it, but with a LOT of help.

But WHY?

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

 

[SkipVought]

You might check the Axis format, for the Dsiplay Units control.

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

 

[SkipVought]

Check out John Peltier's paper of number formatting, toward the bottom find Thousands, Millions, etc.

http://peltiertech.com/Excel/NumberFormats.html

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

 

[tourcd]

Unfortunately I'm working with a very large dataset so no room for additional columns.

 

[zelgar]

I think you only have 2 options.
1. Create a new column having the =B2/2^20 (which is the simpliest solution) & graph
2. Create a macro that will first convert the numbers / 2^20, graph and then run another macro to delete graph & reconvert numbers back to original (note: Not ideal because it could cause rounding errors.)

 

[SkipVought]

Unfortunately I'm working with a very large dataset so no room for additional columns.

"very large" is not quantitative.

Excel 2003 - 256 columns
Excel 2007+ - 16384 columns

So you must exceel one of these column limits, correct?

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

 

[byundt]

Although the better solution is adding an auxiliary column, you might consider plotting a named formula instead of the raw data. The named formula could divide by 1024 so the units are in KB.

To create a named formula:
1. Select the raw data
2. Open the Formulas...Name Manager menu item, and click the New... button
3. Put a name like "Kilobytes" in the Name field
4. Since your data were preselected, just modify the equation in the Refers To field by dividing by 1024. For example:
=Sheet1!$D$2:$D$8/1024

To use the named formula in your chart:
1. Leftclick the series in your chart
2. Edit the series formula in the formula bar to use your newly created named formula. For example:
=SERIES(,Sheet1!$A$2:$A$8,'My Workbook.xlsx'!Kilobytes,1)

 

[mintjulep]

Named Formula. Cool.

Another possible VBA approach:

Excel charts don't actually directly plot the data from a spreadsheet.

They plot values that are stored in an array. The array values can be manipulated with VBA.