Calculation based on what's in a cell

[gleemonex69]

Good afternoon, Excel gods. I have the following data:


A B C D E
01 Name Amount Liter/ML Amount Fl. Oz
02 Tequila 1 Liter 33.814 Fl. Oz.
03 Gin 750 ML 25.3605 Fl. Oz.

What would be the formula if I want Column D to automatically calculate based on whether Column C says either Liter or ML? Your help is greatly appreciated.

 

[Momentman]

Good afternoon, Excel gods. I have the following data:


A B C D E
01 Name Amount Liter/ML Amount Fl. Oz
02 Tequila 1 Liter 33.814 Fl. Oz.
03 Gin 750 ML 25.3605 Fl. Oz.

What would be the formula if I want Column D to automatically calculate based on whether Column C says either Liter or ML? Your help is greatly appreciated.

I really cant make out what you mean by calculate, but if you want a certain result if ML or Liter is in C2 for example then below is how

=IF(C2="Liter", "do something","do something else") ----if the only options are ML or Liter

=IF(C2="Liter","Do something", IF(C2="ML", "Do another","Anything not ML or Liter")) ---if it could ML, Liter or any other

or if you want to check if its either of the two of them then
=IF(OR(C2="Liter",C2="ML"),"Do something","Do something else")

 

[gleemonex69]

Yeah, I don't know why my post looks like that.

Basically, if the Amount is "1" (C4) and Liter/ML is "Liter" (D4) how do I get E4 to calculate correctly if Liter or ML is put in D4? Or if the Amount is "750" (C4) and Liter/ML is "ML" (D4). Basically do conversation to fluid ounces depending on if Liter or ML is put in D4.

 

[jtakw]

Hi,

If Column C can Only be Liter/ML:

Book1
	A	B	C	D	E
1	Name	Amount	Liter/ML	Amount	Fl. Oz.
2	Tequila	1	Liter	33.814	Fl. Oz.
3	Gin	750	ML	25.3605	Fl. Oz.
Sheet351

Cell Formulas
Range	Formula
D2	=IF(C2="ML",B2*33.814/1000,B2*33.814)

 

[gleemonex69]

Hi,

If Column C can Only be Liter/ML:

	A	B	C	D	E
1	Name	Amount	Liter/ML	Amount	Fl. Oz.
2	Tequila	1	Liter	33.814	Fl. Oz.
3	Gin	750	ML	25.3605	Fl. Oz.

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet351​

Worksheet Formulas Cell Formula D2 =IF(C2="ML",B2*33.814/1000,B2*33.814) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thank you guys for your help. I'm sorry I don't know how to add tables like that to my question. But thank you again.

 

[jtakw]

You're welcome, just realized the formula can be shortened to:

Book1
	A	B	C	D	E
1	Name	Amount	Liter/ML	Amount	Fl. Oz.
2	Tequila	1	Liter	33.814	Fl. Oz.
3	Gin	750	ML	25.3605	Fl. Oz.
Sheet351

Cell Formulas
Range	Formula
D2	=B2*33.814/IF(C2="ML",1000,1)

 

[shift-del]

There is a specific function for unit conversion.
But you have use the proper units. In this case l, ml and oz.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E
1	Name	Amount	L/ML	Amount	Fl. Oz.
2	Tequilla	1	l	33,8140227	oz
3	Gin	750	ml	25,360517	oz

<colgroup><col style="width: 28ptpx"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody>
</tbody>

Zelle	Formel
D2	=CONVERT(B2,C2,E2)

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>