Simplifying cost calculation using array instead of IF statement

Hello, I am in the process of calculating the cost of refining precious metals based on user input of specific parameters. For example, if a certain dore intake of Silver has 90% Silver (Ag) content...

excel

Formulas and Functions

m_tarler
Dec 30, 2025
I can't edit that sheet so can't tweak things but what I think you want is something like this:
=SUMPRODUCT(VSTACK(J8/H4,I8/H4,1,1,1,1,1),XLOOKUP(J4,B3:E3,B4:E10,E4:E10,-1))
so the VSTACK will line the AG, AU up with the corresponding rows in the table and the rest of the 1`s will just sum those values. The XLOOKUP will select the corresponding column based on the AG%. I based this the best I could interpret what you did in the equations you have in that sheet. but if not quite right hopefully will at least give you some ideas on how to make a more 'efficient' equation.

ShamsM

Copper Contributor

Dec 30, 2025

Hello Detlef,

The values in Q4 and R4:V4 are correct: The worksheet data from A1 to E10 shows an array description of Ag intake that contains Au as well - often precious metal companies will get scraps with gold and silver mixed and the refiner has to separate the metals. That is why under inputs, the user has inputted 72% of Ag Content and 25% of Au - this intake is primarily silver but has a significant portion of gold as well. Hope this makes sense.

Detlef_Lewin

Silver Contributor

Dec 30, 2025

I wasn't refering to A1:E10 but to H:M.

Forum Discussion

Simplifying cost calculation using array instead of IF statement

Resources