Disk space (Excel 2002)

[fatherjack]

Hi,
i recently found myself creating a spreadsheet of the disk usage on our servers.

Is there a way to format a cell so that it appends the Mb (or Gb) suffix to a figure entered? i have tried via the Cell Format | Number | Custom ... but get an error message.

here's hoping <img src=/S/please.gif border=0 alt=please width=31 height=23>

[HansV]

Do you mean that all numbers will be (for example) in MB, and you want to append MB to the numbers? In that case, set the custom number format to 0 "MB" if you want whole numbers, or 0.0 "MB" if you want one decimal place, etc.

But if you want the suffix to be KB or MB or GB depending on the size of the number (e.g. 1,024 becomes 1 KB and 1,048,576 becomes 1 MB), it's more complicated, but still possible. Post back if this is what you want. (And indicate whether you want 1 KB to be 1,000 or 1,024 bytes, and 1 MB to be 1,000 or 1,024 KB)

[fatherjack]

unbelievable, i forgot the " " around the Mb / Gb. sorry

i only need the single suffix, to be determined by the disk space in use i think.

interested to see how you can conver tfrom one to the other though, how would you do that ? (dont worry about posting it soon, it is only out of interest)

thanks again

[sdckapr]

A "simple way if you want to display converted is to use the custom format:
[>=1000000000]0.0,,," GB";[>=1000000]0.0,," MB";#,##0" KB"

This is NOT strictly true since it uses 1,000 instead of 1,024, but it might be close enough without doing a lot of work.

Steve

[HansV]

Steve already posted the "flexible" format.

[fatherjack]

neat, thank you.

[sdckapr]

I didn't mention and am NOT sure how obvious it is to people, this does NOT change the values ONLY the display.
They are STILL numbers.
So if you do any "math" with the numbers, excel will know that 1.0 GB is 1,000 x the value of 1.0 MB even though they display as they do. That is one of the big advantages of CUSTOM formatting or an IF statement

Steve