/docs/community?hl=en
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
1 Recommended Answer
$0 Recommended Answers
1 Relevant Answer
$0 Relevant Answers
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?

Link to Spread Sheet: https://docs.google.com/spreadsheets/d/1Rwp0i3jMWoEgVfnCd50hifKjTQs0_TPgzKnNMpyQN1E/edit?usp=sharing
Most Relevant Answer Most Relevant Answers (0)
All Replies (2)
Most Relevant Answer
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
marked this as an answer
Most relevant based on info available
Most Relevant Answer
I vote for option #1, the copy down. 
marked this as an answer
Most relevant based on info available
This question is locked and replying has been disabled.
Discard post? You will lose what you have written so far.
Write a reply
10 characters required
Failed to attach file, click here to try again.
Discard post?
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?

A problem occurred. Please try again.
Create Reply
Edit Reply
Delete post?
This will remove the reply from the Answers section.
Notifications are off
Your notifications are currently off and you won't receive subscription updates. To turn them on, go to Notifications preferences on your Profile page.
Report abuse
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Reported post for abuse
Unable to send report.
Report post
What type of post are you reporting?
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Reported post for abuse
Unable to send report.
This reply is no longer available.
/docs/threads
//accounts.google.com/ServiceLogin
You'll receive email notifications for new posts at
Unable to delete question.
Unable to update vote.
Unable to update subscription.
You have been unsubscribed
Deleted
Unable to delete reply.
Removed from Answers
Marked as Recommended Answer
Removed recommendation
Undo
Unable to update reply.
Unable to update vote.
Thank you. Your response was recorded.
Unable to undo vote.
Thank you. This reply will now display in the answers section.
Link copied
Locked
Unlocked
Unable to lock
Unable to unlock
Pinned
Unpinned
Unable to pin
Unable to unpin
Marked
Unmarked
Unable to mark
Reported as off topic
/docs/profile/0?hl=en