Forum Discussion
Simplifying cost calculation using array instead of IF statement
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.
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.