Forum Discussion

ShamsM's avatar
ShamsM
Copper Contributor
Dec 30, 2025
Solved

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's avatar
    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.

m_tarler's avatar
m_tarler
Bronze Contributor
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.

Resources