Need a little help with a simple operation.

[Neomorpheus]

Hello everyone,

I am trying to subtract from two cells that ends in MB, but cant find a way to do this.

Example:

Cell 1 value is 2000 MB, Cell 2 value is 1500 MB.

All i want to do is subtract from Cell 1 using Cell 2 value and return a result also using MB.

No matter what i have tried, i keep getting a #Value error.

Please advice and sorry, but I am a complete noob with Excel.

 

[Finalfight40]

Im sure there is a better way but maybe something like this:

=TEXT(LEFT(A1, SEARCH( " ", A1)), "general")-TEXT(LEFT(B1, SEARCH(" ", B1)), "general") & " MB"

 

[jtakw]

Hi,

Assuming those are TEXT values in the two subject cells:

Book1
	A	B	C
1	2000 MB	1500 MB	500 MB
Sheet131

Cell Formulas
Range	Formula
C1	=LEFT(A1,FIND(" ",A1))-LEFT(B1,FIND(" ",B1))&" MB"

 

[JoeMo]

Another way: Format cells 1 & 2 as; 0" MB"
when you enter 2000 or 1500 in those cells it will appear as 2000 MB and 1500 MB, but cell2 - cell1 will return 500, or if you format the cell holding the formula the same way it will show 500 MB.

 

[Neomorpheus]

Hi,

Assuming those are TEXT values in the two subject cells:

	A	B	C
2000 MB	1500 MB	500 MB

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

</tbody>

Sheet131​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=LEFT(A1,FIND(" ",A1))-LEFT(B1,FIND(" ",B1))&" MB"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Awesome, that did the trick!

Thank you!!!

one more question, can the result of that operation be converted to GB at the same time?

Meaning, adding "/1024" somewhere in it?

 

[jtakw]

Awesome, that did the trick!

Thank you!!!

one more question, can the result of that operation be converted to GB at the same time?

Meaning, adding "/1024" somewhere in it?

You're welcome.

Yes, if you want the result Rounded a certain way, let me know:

Book1
	A	B	C
1	2000 MB	1500 MB	0.48828125 GB
Sheet131

Cell Formulas
Range	Formula
C1	=(LEFT(A1,FIND(" ",A1))-LEFT(B1,FIND(" ",B1)))/1024&" GB"

 

[Neomorpheus]

You're welcome.

Yes, if you want the result Rounded a certain way, let me know:

	A	B	C
2000 MB	1500 MB	0.48828125 GB

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

</tbody>

Sheet131​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=(LEFT(A1,FIND(" ",A1))-LEFT(B1,FIND(" ",B1)))/1024&" GB"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Again, thanks!

Since i dont need the numbers to be that specific, i wouldnt mind not having any decimal points.

Sorry, really an Excel noob

 

[jtakw]

Again, thanks!

Since i dont need the numbers to be that specific, i wouldnt mind not having any decimal points.

Not sure what you mean by "any", because the result will be 0?

I've rounded it to 2 decimal points, change the 2 in the formula to what you want:

Book1
	A	B	C
1	2000 MB	1500 MB	0.49 GB
Sheet131

Cell Formulas
Range	Formula
C1	=ROUND((LEFT(A1,FIND(" ",A1))-LEFT(B1,FIND(" ",B1)))/1024,2)&" GB"

 

[Scott Huish]

You could also substitute the MB out of it:

=ROUND((SUBSTITUTE(A1,"MB","")-SUBSTITUTE(B1,"MB",""))/1024,2)&" GB"

 

[Neomorpheus]

Not sure what you mean by "any", because the result will be 0?

I've rounded it to 2 decimal points, change the 2 in the formula to what you want:

	A	B	C
2000 MB	1500 MB	0.49 GB

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

</tbody>

Sheet131​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=ROUND((LEFT(A1,FIND(" ",A1))-LEFT(B1,FIND(" ",B1)))/1024,2)&" GB"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Actually, that worked perfectly.

Again, thank you very much!!!!