Help with formula for calculating deductions

[fordtough68]

Here is my dilemma.

I offer customers terms to process circuit boards and I have a spreadsheet that calculates what the estimated value will be and and also factors in charges and metal deductions.

For gold, we pay 95% of the gold weight OR 0.5ozt per 2000 lbs. of weight received, whichever is greater.

F29 is the total weight received.
J33 is the value of 5% (this can change occasionally, so I need a formula that references this cell for the % value so I can plug in different %'s)


I have attached an example of what I'm trying to do more precisely. Thanks in advance for any help getting this solved.

[JohnTopley]

Please add expected results.

[fordtough68]

Thanks for your reply.

I added the percentages per grade to the top of the sheet. This is really close to how my sheet calculates and references everything. I've added the two expected outcomes. Please let me know if there is more information that may be needed.

New sheet attached!

[fordtough68]

My apologies, but I mistakenly used my flawed method I am currently trying to fix,....sheesh.

I have reuploaded a new version with all of the CORRECT calculations including all of the conversions from lbs to ounces to troy ounces. You can plug in any number in the "net received" cell (currently 5000) and it automatically populates the correct 5% in the value cell.

SO basically I just need this thing to use the current example OR populate with 0.5 ozt per 200lbs received, whichever is greater. My apologies for the confusion.

[fordtough68]

ok, I think I have everything figured out except the formula itself. This spreadsheet is extremely in depth with tons of references so it is super hard to make a sample sheet. But this may be even better.

What I need is to know how to write this:

M33*U11 OR J33*J31, whichever is greater!!

[fordtough68]

I am thinking that =MAX(M33*U11,J33*J31) should work. Does this look right?

[fordtough68]

final update, the solution I came up with worked like a charm. I suppose I just needed to pull a few more hair out! Thanks for looking into it for me!!