Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Are there any cell custom data formats for MB, GB, and TB storage sizes, especially decimal (used by storage manufacturers) not binary (used by filesystems)?

I'm using Numbers 3.5 to do a quick-and-dirty calculation before I buy an external hard drive. Rather than do each calculation manually I thought it would be better to have a sheet containing prices and drive sizes for every drive I'm considering buying. To accomplish this neatly, and to incorporate multiple orders of magnitude (GB, TB) into the list, it might be useful to define a custom data format to the Numbers app.


Unfortunately, actually creating a custom format of this kind incorporates multiple comparative changes that would be difficult to figure out, and I'm not sure I want to reinvent the wheel if something like this is already in existence.


This explains my question, I think. Are there any Custom Formats available for download which can handle the storage space unit (e.g. GB, TB) of data? This isn't a totally abnormal type of calculation or data type to be handling. A calculation program like Numbers should come equipped with the ability to handle all SI International System of Units measurements and conversions, I think. That or just the most frequently used.


To access the included data formats, select a cell in your table and [click] the [Cell] tab on the right side bar near the top. The first menu is "Data Format" and offers a drop-down list of pre-installed formats. There you will find the "Number", "Currency", "Percentage", etc. formats, but no "-Byte" size.


To the numerically inclined,

Aaron Palmer

MacBook Pro with Retina display, OS X Mavericks (10.9.3)

Posted on Feb 23, 2015 7:21 AM

Reply
10 replies

Feb 25, 2015 5:48 PM in response to yoojinpalmer

Having tried this option, I've also run in to another problem. The maximum number of conditions you can apply is 3, so in my case that would be as follows.


0-999 = kB

If > 1,000, MB

If > 1,000,000, GB

If > 1,000,000,000 TB


This somewhat works, however if you then type in to the cell (10 TB) you get an error, because the TB cannot be parsed as the TB you defined. That means that if you want to enter a 2 TB drive, you can't type in 2tb, you have to type in 2,000,000,000.


The maximum number of conditions also limits the custom formats feature to 4 orders of magnitude, which is not enough for serious computation like what you'd want if you had to also deal with PB etc.

Feb 25, 2015 6:22 PM in response to yoojinpalmer

Yup.


Here is a way you could solve this by adding an extra column and a small lookup table:

User uploaded file


The lookup table is here for the copying:

0




1

k

kilo


2

M

mega


3

G

giga


4

T

tera


5

P

peta


6

E

exa


7

Z

zetta


8

Y

yotta







In table 8 (the one on the left in the image) column A is where the values are.

B1=A1÷(1024^VLOOKUP(INT(LOG(A1, 1024)), Binary Prefixes::A:D, 1))&" "&VLOOKUP(INT(LOG(A1, 1024)), Binary Prefixes::A:D, 2)&"B"


this is shorthand for... select cell B1, then type (or copy and paste from here) the formula:

=A1÷(1024^VLOOKUP(INT(LOG(A1, 1024)), Binary Prefixes::A:D, 1))&" "&VLOOKUP(INT(LOG(A1, 1024)), Binary Prefixes::A:D, 2)&"B"


select cell B1, copy,

now select all the cells in column B, paste

Feb 25, 2015 6:58 PM in response to yoojinpalmer

User uploaded file

Here is the correct way to use the conditional formatting, I think. I added conditional formats in descending sizes so that the larger numbers don't too easily qualify in another format, and then used the Scale: (K) function in orders of Billions Millions and Thousands to appropriately truncate the number. Since I was using decimal format it was a simple matter of using conditional formatting without needing to resort to the lookup table, which could be difficult to incorporate into the spreadsheet if you aren't a rocket scientists. Multiple rocket scientists.. Anyways, it wasn't too hard, but I still have a complaint to restate... Data entry in this way doesn't feel right. I wish it would contextualize the entry in such a way as to JUST LET ME PUT 3GB = XXX (millions) instead of having to enter 3,000,000 for that.

Are there any cell custom data formats for MB, GB, and TB storage sizes, especially decimal (used by storage manufacturers) not binary (used by filesystems)?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.