Forum Discussion
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.
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.
5 Replies
Hi,
Maybe I'm wrong, but it looks like you asked the same question in another Microsoft forum (https://learn.microsoft.com/en-us/answers/questions/5685735/simplifying-cost-calculation-using-array-instead-o) and marked the answers given to you by the contributors to that forum.
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.
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.
I wasn't refering to A1:E10 but to H:M.
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.
