Help with a unit convert table with formulas and multiple index/match criteria

[StrawberryDreams]

I've built a table that converts the value based on the Original Unit and the Converted to Unit. The New cell value relies on another table that has the multiple possibilities and results that are used to pull from using index match.

The Element table allows the user to select an Element and its Value. As it stands everything works but I don't know how to expand the Element table to do another conversion for another element without getting a spill error ( Convert Table would have to have each of the 9 possible formula results written for each possible Element Name). I could do that but would like to learn if there is a better more elegant way to do this even in VBA if that makes it easier ?

Basic data calculator test 9.9b.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Original Unit					Element	Atomic Weight	Valence			mmol/L = ppm / atomic weight
2	mmol/L					N	14	1			meq/L = ppm * Valence / Atomic Weight
3	Convert to Unit					Ca	30	2			ppm = meq/L * Atomic Weight / Valence
4	meq/L
5						Convert Table
6						Element Choosen	Original Unit	Convert to Unit	New Value Result		Convert formulas for New Value Result
7						Ca	ppm	ppm	120		No conversion needed - Units match
8	Element	Value	New Value			Ca	ppm	mmol/L	4		mmol/L = Cell Value * Atomic weight
9	Ca	120	240			Ca	ppm	meq/L	8		meq/L = (Cell Value * Valence) / Atomic Weight
10
11						Ca	mmol/L	mmol/L	120		No conversion needed - Units match
12						Ca	mmol/L	ppm	3600		ppm = Cell Value * Atomic Weight
13						Ca	mmol/L	meq/L	240		meq/L = Cell Value * Valence
14
15						Ca	meq/L	meq/L	120		No conversion needed - Units match
16						Ca	meq/L	ppm	1800		ppm = Cell Value * Atomic Weight / Valence
17						Ca	meq/L	mmol/L	60		mmol/L = Cell Value / Valence
Sheet1

Cell Formulas
Range	Formula
C9	C9	=INDEX(Table26[New Value Result],MATCH(1,(Table26[Original Unit]=A2)*(Table26[Convert to Unit]=A4),0))
F7:F8,F15:F17,F11:F13	F7	=Table24[Element]
F9	F9	=Table24[@Element]
I7,I15,I11	I7	=Table24[Value]
I8	I8	=Table24[Value] / INDEX(Table25[Atomic Weight],MATCH(Table24[Element],Table25[Element],0))
I9	I9	=Table24[Value] * INDEX(Table25[Valence],MATCH(Table24[@Element],Table25[Element],0)) / (INDEX(Table25[Atomic Weight],MATCH(Table24[Element],Table25[Element],0)))
K11,K15	K11	=K7
I12	I12	=Table24[Value]* INDEX(Table25[Atomic Weight],MATCH(Table24[Element],Table25[Element],0))
I13	I13	=Table24[Value] * INDEX(Table25[Valence],MATCH(Table24[Element],Table25[Element],0))
I16	I16	=Table24[Value] * INDEX(Table25[Atomic Weight],MATCH(Table24[Element],Table25[Element],0)) / INDEX(Table25[Valence],MATCH(Table24[Element],Table25[Element],0))
I17	I17	=Table24[Value] / INDEX(Table25[Valence],MATCH(Table24[Element],Table25[Element],0))

Cells with Data Validation
Cell	Allow	Criteria
A4:A5	List	ppm, mmol/L, meq/L
A2	List	ppm, mmol/L, meq/L
A9	List	N,Ca

 

[shift-del]

Hello S...

What about a lambda?

Mappe7
	A	B	C	D	E	F	G	H	I
1	Original Unit		Element	Atomic Weight	Valence		Element	Value	New Value
2	mmol/L		N	14	3		Ca	120	240
3	Convert to Unit		Ca	30	2		N	14	42
4	meq/L
5
Tabelle1

Cell Formulas
Range	Formula
I2:I3	I2	=convert_waterchemistry($A$2,$A$4,[@Element],[@Value])

Hmm... XL2BB didn't insert the named ranges.

Name: convert_waterchemistry
Formula:

=LAMBDA(from_unit,to_unit,element,value,
LET(
atomic_weight,XLOOKUP(element,Table25[Element],Table25[Atomic Weight]),
valence,XLOOKUP(element,Table25[Element],Table25[Valence],
ppm_mmol,value*atomic_weight,
ppm_meq,value*valence/atomic_weight,
mmol_ppm,value*atomic_weight,
mmol_meq,value*valence,
meq_ppm,value*atomic_weight/valence,
meq_mmol,value/valence,
i,MATCH(from_unit&"_"&to_unit,{"ppm_mmol/L";"ppm_meq/L";"mmol/L_ppm";"mmol/L_meq/L";"meq/L_ppm";"meq/L_mmol/L"},0),
new_value,CHOOSE(i,ppm_mmol,ppm_meq,mmol_ppm,mmol_meq,meq_ppm,meq_mmol), new_value))

 

[StrawberryDreams]

Hello S...

What about a lambda?

Mappe7
	A	B	C	D	E	F	G	H	I
1	Original Unit		Element	Atomic Weight	Valence		Element	Value	New Value
2	mmol/L		N	14	3		Ca	120	240
3	Convert to Unit		Ca	30	2		N	14	42
4	meq/L
5
Tabelle1

Cell Formulas
Range	Formula
I2:I3	I2	=convert_waterchemistry($A$2,$A$4,[@Element],[@Value])

Hmm... XL2BB didn't insert the named ranges.

Name: convert_waterchemistry
Formula:

=LAMBDA(from_unit,to_unit,element,value,
LET(
atomic_weight,XLOOKUP(element,Table25[Element],Table25[Atomic Weight]),
valence,XLOOKUP(element,Table25[Element],Table25[Valence],
ppm_mmol,value*atomic_weight,
ppm_meq,value*valence/atomic_weight,
mmol_ppm,value*atomic_weight,
mmol_meq,value*valence,
meq_ppm,value*atomic_weight/valence,
meq_mmol,value/valence,
i,MATCH(from_unit&"_"&to_unit,{"ppm_mmol/L";"ppm_meq/L";"mmol/L_ppm";"mmol/L_meq/L";"meq/L_ppm";"meq/L_mmol/L"},0),
new_value,CHOOSE(i,ppm_mmol,ppm_meq,mmol_ppm,mmol_meq,meq_ppm,meq_mmol), new_value))

I had found a mistake in my original index match formulas not referencing the proper table... so Im rewriting it now and can resubmit when fully fixed.

 

[StrawberryDreams]

Hello S...

What about a lambda?

Mappe7
	A	B	C	D	E	F	G	H	I
1	Original Unit		Element	Atomic Weight	Valence		Element	Value	New Value
2	mmol/L		N	14	3		Ca	120	240
3	Convert to Unit		Ca	30	2		N	14	42
4	meq/L
5
Tabelle1

Cell Formulas
Range	Formula
I2:I3	I2	=convert_waterchemistry($A$2,$A$4,[@Element],[@Value])

Hmm... XL2BB didn't insert the named ranges.

Name: convert_waterchemistry
Formula:

=LAMBDA(from_unit,to_unit,element,value,
LET(
atomic_weight,XLOOKUP(element,Table25[Element],Table25[Atomic Weight]),
valence,XLOOKUP(element,Table25[Element],Table25[Valence],
ppm_mmol,value*atomic_weight,
ppm_meq,value*valence/atomic_weight,
mmol_ppm,value*atomic_weight,
mmol_meq,value*valence,
meq_ppm,value*atomic_weight/valence,
meq_mmol,value/valence,
i,MATCH(from_unit&"_"&to_unit,{"ppm_mmol/L";"ppm_meq/L";"mmol/L_ppm";"mmol/L_meq/L";"meq/L_ppm";"meq/L_mmol/L"},0),
new_value,CHOOSE(i,ppm_mmol,ppm_meq,mmol_ppm,mmol_meq,meq_ppm,meq_mmol), new_value))

this looks interesting... trying to implement it and I'm guessing I have to create named ranges and assign them to each table column ref.

Do I insert this large lambda formula in the name manager refers to location box ?

 

[StrawberryDreams]

So I named the ranges ; element, atomic_weight, valence, and value. Then I created another name called water_chemistry and entered the Lambda formula you made and got an error saying - " You've entered too many arguments. for this function." I must be missing something else I need to name ?

 

[shift-del]

You only need the defined name "convert_waterchemistry". All the other names are set in LAMBDA() or LET().

 

[StrawberryDreams]

I had found a mistake in my original index match formulas not referencing the proper table... so Im rewriting it now and can resubmit when fully fixed.

You only need the defined name "convert_waterchemistry". All the other names are set in LAMBDA() or LET().

I must be missing something or not entering it properly, or maybe being on a mac has limits ?

still getting the error when trying to enter in name manager.

 

[StrawberryDreams]

I deleted all the previous named ranges and only tried to use the convert_waterchemistry..... and still.

Basic data calculator test 9.9bc.xlsm
	A	B	C	D	E	F	G	H	I
1	Original Unit		Element	Atomic Weight	Valence		Element	Value	New Value
2	ppm		Total N	14.0067	1		P	120	#NAME?
3	Convert to Unit		NO3-	14.0067	1		Ca	100	#NAME?
4	meq/L		NH4	14.0067	1
5			P	30.973762	1		=LAMBDA(from_unit,to_unit,element,value, LET( atomic_weight,XLOOKUP(element,Table25[Element],Table25[Atomic Weight]), valence,XLOOKUP(element,Table25[Element],Table25[Valence], ppm_mmol,value*atomic_weight, ppm_meq,value*valence/atomic_weight, mmol_ppm,value*atomic_weight, mmol_meq,value*valence, meq_ppm,value*atomic_weight/valence, meq_mmol,value/valence, i,MATCH(from_unit&"_"&to_unit,{"ppm_mmol/L";"ppm_meq/L";"mmol/L_ppm";"mmol/L_meq/L";"meq/L_ppm";"meq/L_mmol/L"},0), new_value,CHOOSE(i,ppm_mmol,ppm_meq,mmol_ppm,mmol_meq,meq_ppm,meq_mmol), new_value))
6			K	39.0983	1
7			S	32.065	2
8			Ca	40.078	2
9			Mg	24.305	2
10			Fe	55.845	2
11			Mn	54.938044	2
12			B	10.811	2
13			Cu	63.546	2
14			Zn	65.38	2
15			Mo	95.95	1
16			Cl	35.453	1
17			Si	28.0855	4
18			Co	58.933195	1
19			Na	22.989769	1
20			Ni	58.6934	1
ppm to mmols (2)

Cell Formulas
Range	Formula
I2:I3	I2	=convert_waterchemistry($A$2,$A$4,[@Element],[@Value])

Cells with Data Validation
Cell	Allow	Criteria
A2	List	ppm, mmol/L, meq/L
A4	List	ppm, mmol/L, meq/L

 

[Cubist]

See if this works for you.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Original Unit		Element	Atomic Weight	Valence		Element	Original	Covert	Value	New Value
2	ppm		Total N	14.0067	1		Ca	ppm	ppm	120	120
3	Convert to Unit		NO3-	14.0067	1		Ca	ppm	mmol/L	100	4008
4	meq/L		NH4	14.0067	1		Ca	ppm	meq/L	120	6
5			P	30.973762	1		Ca	mmol/L	mmol/L	100	100
6			K	39.0983	1		Ca	mmol/L	ppm	120	4809
7			S	32.065	2		P	mmol/L	meq/L	100	100
8			Ca	40.078	2		Ca	meq/L	meq/L	120	120
9			Mg	24.305	2		Mg	meq/L	ppm	100	1215
10			Fe	55.845	2		Ca	meq/L	mmol/L	120	60
11			Mn	54.938044	2
12			B	10.811	2
13			Cu	63.546	2
14			Zn	65.38	2
15			Mo	95.95	1
16			Cl	35.453	1
17			Si	28.0855	4
18			Co	58.933195	1
19			Na	22.989769	1
20			Ni	58.6934	1
Sheet13

Cell Formulas
Range	Formula
K2:K10	K2	=LET(w,XLOOKUP(G2,$C$2:$C$20,$D$2:$D$20),v,XLOOKUP(G2,$C$2:$C$20,$E$2:$E$20), oldval,J2, SWITCH(H2&I2,"ppmmmol/L",oldval*w,"ppmmeq/L",(oldval*v)/w,"mmol/Lppm",oldval*w,"mmol/Lmeq/L",oldval*v,"meq/Lppm",oldval*w/v,"meq/Lmmol/L",oldval/v,oldval))

 

[shift-del]

I deleted all the previous named ranges and only tried to use the convert_waterchemistry..... and still.

Sorry, I have no idea.