Recalculating Data Unit values

[ldoodle]

Hey,

Let's say I have this:

A1: 10GB
B1: =CONVERT(A1,"Gibyte","byte")

That will give me the exact byte value of 10GB. However, I want to step up to next "whole" byte; "whole" will depend on the unit in A1:

A1: 640.5
B1: =CONVERT(A1,"Gibyte","byte")

A1: 10.5GB
B1: =CONVERT(A1,"Gibyte","byte")

That will give me 800.5MB and 10.5GB in byte value, but I'd want those to be stepped up to 641GB and 11GB (possibly even 642GB and 12GB to avoid odd number) in byte notation. However it's not as simple as rounding to 1 decimal, since this one is allowable:

A1: 1.5TB
B1: =CONVERT(A1,"Tibyte","byte")

But this one isn't:

A1: 1.57TB
B1: =CONVERT(A1,"Tibyte","byte")

That should be rounded to 1.6TB in bytes.

This is for VM sizing, e.g. you'd never spec a VM RAM with decimals in (10.5GB) but you would give it 1.5TB. Out of interest I am obviously going based on 1024 (binary) instead of 1000 (decimal). Not sure whether this is the correct way or not. Some Hypervisors still require MB notation for RAM, e.g. 10240MB for 10GB.

(I'm being cleverer than shown in the CONVERT function to actually determine the source format (B, KB, MB, GB, TB, PB, EB, ZB, YB).)

Thanks

 

[Bosquedeguate]

try
B1: = CONVERT(INT(A1 + .9), ..._ in your formula.

 

[ldoodle]

try
B1: = CONVERT(INT(A1 + .9), ..._ in your formula.

Thanks. That seems OK for GB, but for TB it rounds to next whole number.

55.5GB becomes 56GB = good
1.5TB becomes 2TB = not good

 

[Bosquedeguate]

Thanks. That seems OK for GB, but for TB it rounds to next whole number.

55.5GB becomes 56GB = good
1.5TB becomes 2TB = not good

You can use a modified version of this formula for TB
B1: = CONVERT(INT(A1*10 + 0.9)/10, "Tibyte","byte") in your formula

However, if you provide the code for your CONVERT function the "rounding" calculation could be part of that formula, or maybe there is a better solution than what I have provided.

Samples of your unconverted data would be very helpful as well.

 

[Bosquedeguate]

You can use a modified version of this formula for TB
B1: = CONVERT(INT(A1*10 + 0.9)/10, "Tibyte","byte") in your formula

However, if you provide the code for your CONVERT function the "rounding" calculation could be part of that formula, or maybe there is a better solution than what I have provided.

Samples of your unconverted data would be very helpful as well.

Here is a sample of another way to do your conversion and format of the Memory values. All of the columns would not be necessary. I include them so you could, hopefully, see how I did the conversion.

Book3
	A	B	C	D
3	RAM	MIN RAM BYTES	10^(n*3) DIVISOR	CONVERTED
4	10	1024	1	1.0 KB
5	100	1024	1	1.0 KB
6	1000	1024	1	1.0 KB
7	2000	2048	1	2.0 KB
8	10000	10240	1	10.2 KB
9	15000	15360	1	15.4 KB
10	100000	100352	1	100.4 KB
11	200000	200704	1	200.7 KB
12	900000	900096	1	900.1 KB
13	500000	500736	1	500.7 KB
14	1000000	1000448	2	1.0 MB
15	2000000	2000896	2	2.0 MB
16	8000000	8000512	2	8.0 MB
17	9000005	9000960	2	9.0 MB
18	10000000	10000384	2	10.0 MB
19	50000000	50000896	2	50.0 MB
20	90000000	90000384	2	90.0 MB
21	100000000	100000768	2	100.0 MB
22	500000000	500000768	2	500.0 MB
23	900000000	900000768	2	900.0 MB
24	1000000000	1000000512	3	1.0 GB
25	7000000000	7000000512	3	7.0 GB
26	9000000000	9000000512	3	9.0 GB
27	10000000000	10000000000	3	10.0 GB
28	90000000000	90000000000	3	90.0 GB
29	1E+11	1E+11	3	100.0 GB
30	9E+11	9E+11	3	900.0 GB
31	1E+12	1E+12	4	1.0 TB
32	9E+12	9E+12	4	9.0 TB
Sheet6

Cell Formulas
Range	Formula
B4:B32	B4	=INT((A4+1023)/1024)*1024
C4:C32	C4	=INT(INT(LOG10(B4))/3)
D4:D32	D4	=TEXT(B4/(10^(C4*3)),"0.0 ") & CHOOSE(C4,"KB","MB","GB","TB")