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.
1 Reply
Unless I'm mistaken, after spending a bit of time looking at your formulae, I think you could take good advantage of INDEX and MATCH to zero in on the relevant cells in this table
These hyperlinks will point you to a good reference site to learn how INDEX and MATCH work. And again, unless I'm mistaken, you would be able to eliminate the many IFS conditions comparing J4 with the top row to directly zero in on the appropriate column, and then again to identify the appropriate row based on the items down the left of this table.
Given how well you've mastered IFS and XLOOKUP in combination I have no doubt that you'll be able to use INDEX and MATCH equally well.
One other recommendation, assuming you are working in Excel rather than Google Sheets, you could use (on a Mac) a combination of the Option key and "Return" to format your extensive formulas as I've done with the formula in Q12, below; they're much more readable this way.
