This content is likely not relevant anymore. Try searching or browse recent questions.
-
Need Help Building Weird Calculator 0 Recommended Answers 2 Replies 0 Upvotes
Hi, I'm trying to build a very specific calculator. In this calculator, there are 3 possible prices for every item. The calculator is supposed to multiply the quantity of that item by the cheapest available price, and then reduce that value by 25%. The "total" column simply add the costs. The basic logic is:

Batch Cost = (quantity of Item A) * (cheapest price for item A) - 25%

The formula I'm using to do this is:

=C:C*MIN(F:F,G:G,H:H)*0.75

The only problem is, my formula isn't using the cheapest price for each item. My formula is using the cheapest price in general. I can't limit the MIN function to the correct row. Can you help me?

Details
All Replies (2)
Hello Philip,

Your sheet is set to view only so you'll have to give this a try and see if it works. I have a couple options. The first is to clear D2:D and place this in D2, =C2*MIN(F2:H2)*0.75 this formula can then be copied down for as far as you need. This will adjust to the row it is in since the references are relative.

The second option is more complicated but will only need one formula in D2. For this one you will also need to clear D2:D and them place this in D2.

=ArrayFormula(C2:C*QUERY(TRANSPOSE(QUERY(TRANSPOSE(F2:H),"select "&SUBSTITUTE(TRIM(QUERY(IF(B2:B="",,"Min(Col"&ROW(C1:C)&")")&CHAR(10),,9^99)),CHAR(10),",")&"",0)),"select Col2 where Col1 is not null",0)*0.75)

This uses a little trick using this part SUBSTITUTE(TRIM(QUERY(IF(B2:B="",,"Min(Col"&ROW(C1:C)&")")&CHAR(10),,9^99)),CHAR(10),",") to build a string that reads Min(Col1),Min(Col2),Min(Col3) and so on until it has the correct number of Col's to be used in the QUERY() which then uses to pull the minimum for each column which is built from the rows being TRANSPOSE()'d before the QUERY() and then again after the QUERY() and then the calculations are done.

Hope this helps,
Stephen
Most relevant based on info available
I vote for option #1, the copy down.
Most relevant based on info available
This question is locked and replying has been disabled.
10 characters required
You will lose what you have written so far.
Personal information found

We found the following personal information in your message:

This information will be visible to anyone who visits or subscribes to notifications for this post. Are you sure you want to continue?

Delete post?