Back with a question
I am looking for a formula to extract the minimum and second lowest values from an array if there are multiple criteria like below:
Extract the minimum value from array, say A3:F3 - Note: between these cells there are texts.
We need to consider only the cells if the adjacent cell has text and also the relevant column header is "Unit Price- AD"Untitled.jpg
Need similar formula for 2nd lowest.
Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution is also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as they cause lots of problems.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Try with
Formula:
Please Login or Register to view this content.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Not able to attach the file. Some IT issues.
What IT issues? If you mean the paperclip icon, it doesn't work, as I explained in my post. I also explained what does work.
Have you tried AVK's suggestion?
Maybe?
=SMALL(IF($A$2:$U$2="Unit Price- AD",A3:U3),1)
Enter with Ctrl+Shift+Enter.
Still not able to upload the file. I cannot see Manage attachments option
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Clcik on Choose File : From your respected folder select file & click on "open"
>Click on "upload" tab
>After completed upload, click on "close this window
>Click on "Submit Reply"
Here you go
You ask "Extract the minimum and second lowest numbers"
Where as per attach file you asking extract as per "Unit Pirce-AD"
Can you manually enter your required result & where (in which cell) confirm.
Perhaps this:
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Please look at the upper row. there are 4 vendors which I mentioned in the top row.
I need to get the minimum and second lowest values of 'Unit Price-AD' only if there is a TEXT in 'Remarks' (adjacent cell).
It doesn't work properly. I have already tried this.Originally Posted by AliGW
Even if you removed the remarks "GE" in the adjacent cell, you will get the same value "3".
What should the answer be if that cell is blank?
Blank or Zero.
This maybe?
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
using CTRL+SHIFT+ENTER.
From Ali's Formula in #11 this will work for all text like GE and KLT
Formula:Please Login or Register to view this content.
IF the structure of your data are all the same
you have
Unit Pirce-AD Unit Pirce-DXB Remarks
FOR EVERY VENDOR
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
I think this you required.
If "Unit Pirce-AD" find & extract second lowest number.
In "C5" criteria : Unit Pirce-AD
In "D5" Formula :
Formula:Please Login or Register to view this content.
Thanks for the rep - glad to have helped!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks