Forum Discussion

ShamsM's avatar
ShamsM
Copper Contributor
Dec 30, 2025

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 then lookup the specific processes and multiply the cost per oz with the intake ounces. I have attempted to combine IFS and Xlookup for each process separately but the formula looks very unwieldy. I am also enclosing a slightly simpler formula of IFS and sum where the total cost is calculated in one cell (Q12).

Here is the link: https://docs.google.com/spreadsheets/d/1hizmF6EwhxOPEeR10bXJsBOKeOtXude8/edit?usp=drive_link&ouid=103354753371375324640&rtpof=true&sd=true

I am looking to see if I can have a more dynamic iteration of the formula in Cell Q12 as well as in the calculation of the individual processes in Row 4 , Cols P:V. Thank you.

Regards,

Shams.

excel
Formulas and Functions

2 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Dec 30, 2025

    I wonder if your logic is correct.

    In P4 (Ag) you are getting the values attributed to Ag (J4/J8). That seems okay.

    In Q4 (Au) you are getting the values attributed to Ag (J4) and Au (J8). That seems not okay. Your mixing values from Ag/Au.

    In R4:V4 you are getting the values attributed to Ag (J4). That seems not okay. The processes are not related to Ag specifically.

  • 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