I have three columns (let's say A1:C10) filled with numbers ranging from 1-20.
How can I get in D1 the smallest value from C column, but only from the rows that have both A and B >10?
I'm guessing it will include a combination of small and if but I don't know how to make it work.
Thanks
Try:
=MIN(IF((A1:A10>10)*(B1:B10>10),C1:C10))
confirmed with CTRL+SHIFT+ENTER not just ENTER.
If you get error entering formula, change the comma to a semi-colon.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I tried it, changed coma to cemicolon, but it gives me the wrong number.
The two first numbers - I want them to be (>10) - of the row are 5(<10) and 13.
After you enter the formula, hold the CTRL and SHIFT keys down.
Then hit ENTER. You should see { } brackets appear around the formula.
Does that new number meet the requirement? I got 8 as a result.
Wow. Thank you very much. I'm new, so allow me a question.
What is the purpose of brackets?
It tells Excel that this is an ARRAY formula, allowing it to proces all the elements in the range instead of just 1...
See this article on Array Formulas.
OK. Thanks and Happy New Year
Same to you.
I thought of another problem.
What happens if I want to display the whole row of the minimum.
Let's say I want to display the row in A12,B12,C12 (one row below the columns of numbers)
In the C12 I would type: =MIN(IF((A1:A10>10)*(B1:B10>10);C1:C10))
confirmed with CTRL+SHIFT+ENTER not just ENTER
I thought I could type in A12 : =IF((C1:C10=C12);A1:A10),
to compare the values in C column with the minimum I found and return the value of the column A from the same row, but I'm getting FALSE
I hope you understand what I want to do
Try
confirmed with CSE keys again and copied to next column to get B value
Please Login or Register to view this content.
Note: To avoid double calcs you can replace
with Cell reference that has that result alreadyPlease Login or Register to view this content.
It took a while to check the syntax of the functions, and I finally understood.
However it says that the formula contains an error.
I'm sending once more the file in case that helps you.
If you have the patience respond.
Thanks
Did you change the commas to semi-colons? Then remember to hold the CTRL and Shift keys down and press ENTER.
Last edited by NBVC; 01-15-2011 at 06:13 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks