Formula for Data Sizes (B, KB, MB, GB)

[ildanach]

Hey folks,

I use an application that reports data size usage by date. You can see the screenshot below of how it exports its results to excel.

I was wondering if there was a way to right a formula that will ignore the 'B', 'KB', 'MB' and calculate the total data size in MB's or GB's using the 1024b per meg and so on.

If not I was wondering is it possible to create my own custom data type where I can specify the parameters for calculating in data sizes?

Thanks a million in advance!
ildanach

 

[dafan]

Do you mean something like this? This will only sum up the values though, doesn't see if it's MB or GB. Will try to work something out now!

Excel Workbook
	A	B
1		Usage
2	Monday	140.86 MB
3	Tuesday	500 MB
4	Wednesday	254.87 MB
5	Thursday	1.45 MB
6	Friday	87.14 MB
7
8	Total	984.32
Sheet1

 

[Stormseed]

Perhaps,

=IF(RIGHT(B2,2)="mb",SUMPRODUCT(--(LEFT(B2:B6,FIND(" ",B2:B6)-1)))*1024)

 

[dafan]

Here we go:

Excel Workbook
	A	B
1		Usage
2	Monday	140.86 MB
3	Tuesday	512 B
4	Wednesday	512 KB
5	Thursday	1.45 GB
6	Friday	87.14 MB
7
8	Total	1713.3005
Sheet1

This will give the total size in MB! If you want if displayed in GB, divide by 1024 etc..

 

[ildanach]

Thanks a million :P

That helps so much....is there any way to add the mb or gb

 

[dafan]

My last reply does see the diff between B/KB/MB and GB.

 

[pgc01]

Hi ildanach
Welcome to the board

Another option:

=SUM(LEFT(B2:B6,FIND(" ",B2:B6))*IF(RIGHT(B2:B6,2)={"GB","MB","KB"," B"},1024^{1,0,-1,-2}))
Confirmed with CTRL-SHIFT-ENTER

 

[dafan]

Hi ildanach
Welcome to the board

Another option:

=SUM(LEFT(B2:B6,FIND(" ",B2:B6))*IF(RIGHT(B2:B6,2)={"GB","MB","KB"," B"},1024^{1,0,-1,-2}))
Confirmed with CTRL-SHIFT-ENTER

That's awesome. Great work!

 

[nepalihero]

Here we go:

Sheet1

*	A	B
*	Usage
Monday	140.86 MB
Tuesday	512 B
Wednesday	512 KB
Thursday	1.45 GB
Friday	87.14 MB
*	*
Total

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:80px;"><col style="width:69px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]1713.3005[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B8 {=SUMPRODUCT(--(LEFT(B2:B6,FIND(" ",B2:B6)-1))*(IF(RIGHT(B2:B6,2)="MB",1,IF(RIGHT(B2:B6,2)="GB",1024,IF(RIGHT(B2:B6,2)="KB",1/1024,IF(RIGHT(B2:B6,2)=" B",1/(1024^2),0))))))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

=LEFT(A1,LEN(A1)-2)/1024^((MATCH(RIGHT(A1,2),{"PB","TB","GB","MB","KB"},0)-3))

This will give the total size in GB! If you want if displayed in MB, change last -3 to -4 or to Kb then -5 and so on

 

[gunnarh]

When i try this formula i always get

There´s a probelm with this formula
Not trying to type a formula?
When the first charaters......


Any idea?