Use parsed values in formula

[mejohn]

Hello,

I'm trying to calculate the volume of a solution based on the mass, components, ratio of components and density.
I've entered formulas in column E, but would like a formula that can account for single or multiple components in the solution, as well as their respective ratios and densities.
I'd like to use column B to find the component, column C to find ratio of components and match those with column F and G to determine the density to be used in the calculation.

Thank you in advance for taking the time help me with this important problem.

A (sample)	B (component/s)	C (component ratio)	D (mass (g))	E (volume (ml))	F (component)	G (density)
solution1	NotWater1:Water	55:45	350	=(D1*0.55/0.75)+(D1*0.45/1)	Water	1
solution2	NotWater2:NotWater1	83:17	423	=(D2*0.83/0.96)+(D2*0.17/0.75)	NotWater1	0.75
solution3	NotWater2	1	525	=(D3*1/0.96)	NotWater2	0.96

 

[JamesCanale]

The colon's in column C are problematic - they want to make the data into 'time'. So I switched to "-" there.

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G
1	A (sample)	B (component/s)	C (component ratio)	D (mass (g))	E (volume (ml))	F (component)	G (density)
2	solution1	NotWater1:Water	55-45	350	414.167	Water	1
3	solution2	NotWater2:NotWater1	83-17	423	461.599	NotWater1	0.75
4	solution3	NotWater2	1	525	546.875	NotWater2	0.96
Sheet14

Cell Formulas
Range	Formula
E2:E4	E2	=IF(C2=1,(D2*C2/G2),(D2*(LEFT(C2,SEARCH("-",C2)-1)/100)/VLOOKUP(LEFT(B2,SEARCH(":",B2)-1),$F$2:$G$4,2,FALSE))+(D2*(RIGHT(C2,LEN(C2)-SEARCH("-",C2))/100)/VLOOKUP(RIGHT(B2,LEN(B2)-SEARCH(":",B2)),$F$2:$G$4,2,FALSE)))

 

[mejohn]

Exactly what I needed.
Thanks so much.