Simplifying cost calculation using array instead of IF statement

Question

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.

Answer 1

Hi @Shams Mahmud,

Thank you for posting your question in the Microsoft Q&A forum.

Mr. Ashish Mathur did provide you a dynamic formula for cell Q12 so you can try that to see if it helps. About the calculation of the individual processes in Row 4 columns P:V, since you can try this formula:

• For refining costs of Au and Ag per ounce: =XLOOKUP(P3,$A$4:$A$10,CHOOSECOLS($B$4:$E$10,MATCH($J$4,$B$3:$E$3,-1)))*I8/$H$4

• For the other costs: =XLOOKUP(R3,$A$4:$A$10,CHOOSECOLS($B$4:$E$10,MATCH($I$4,$B$3:$E$3,-1)))*$H$4/$H$4

Note: I did change the order of Refining costs of Au and Ag for better compatibility between the data ranges so it may look a bit different compared to your worksheet.

I hope this information is helpful. Please try the formulas and let me know if it works for you. If not, we can work together to find a solution.  

Thank you for your patience and your understanding. If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.    

Best wishes to you and your family for a wonderful and prosperous New Year.

Looking forward to your response.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

Use this formula

=LET(s,J4,r,B3:E3,SUM(XLOOKUP(s,r,B6:E10,,1))+SUM(INDEX(TOROW(XLOOKUP(s,r,B4:E5,,1)),1,{2,1})*(I8:J8))/H4)

Hope this helps.

Answer 3

To simplify your cost calculation for refining precious metals, you can consider using array formulas instead of multiple nested IF statements. Here’s a general approach you can take:

1. Use Array Formulas: Instead of using multiple IFS, you can create an array formula that evaluates the conditions and returns the corresponding costs based on the silver content. For example, if you have a list of costs per ounce in one range and the corresponding silver content in another, you can use the SUMPRODUCT function to calculate the total cost dynamically. Example formula:

      =SUMPRODUCT((SilverContentRange=90%)*(CostPerOunceRange), IntakeOunces)
   

   This formula multiplies the cost per ounce by the intake ounces for only those entries where the silver content matches 90%.
2. Dynamic Ranges: Use dynamic named ranges or structured table references to ensure that your formulas automatically adjust as you add or remove data. This can help maintain performance and readability.
3. Helper Columns: If needed, create helper columns that calculate intermediate values (like the cost per process) and then reference these in your main calculation. This can help keep your main formula cleaner and easier to understand.
4. Consider Using XLOOKUP: If you are using Excel 365 or Excel 2021, XLOOKUP can simplify your lookup processes significantly. You can use it to find the cost per ounce based on the silver content directly without needing to nest multiple functions.

By implementing these strategies, you should be able to create a more dynamic and efficient calculation for your cost analysis.

---

References:

• Excel performance: Tips for optimizing performance obstructions
• How to use a logical AND or OR in a SUM+IF statement in Excel