index, max, small, if

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Try this array formula:

=IFERROR(INDEX($A$1:$C$1,MATCH(MAX(IF(E2:G2=0,IF(A2:C2>2,A2:C2))),IF(E2:G2=0,A2:C2),0)),"")

You may need to change , to ; depending on your regional settings.


Array Formulae are a little different from ordinary formulas in that they MUST be 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 the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

I've just seen AliGW's post. She's right. Cross-posting is a nuisance. I may have wasted my time giving you an answer that you already have. Please include links in future.

first sorry for not posting the link to other forum thread
http://www.ozgrid.com/forum/showthread.php?t=200626

second thank you Glenn very much for the solution , especially since its a sunday

third if you can help me with modifying the formula for asking the minimun value this time and changing one of the criteria

my modification doesnt seem to work

=IFERROR(INDEX($A$1:$C$1,MATCH(MIN(IF(E2:G2>0,IF(A2:C2>2,A2:C2))),IF(E2:G2>0,A2:C2),0)),"")

What are the criteria now?
The minimum value of A to C, where E to G must be positive AND A to C must be greater than 2... or something else?

I see that you're in Tirana - a nice spot. I've been there twice, the last time in Dec 2014. I spent an enjoyable week working there, staying in Rruga Brigada VIII in the Blloku area. Lots of nice restaurants and pubs !!

Originally Posted by Glenn Kennedy

I see that you're in Tirana - a nice spot. I've been there twice, the last time in Dec 2014. I spent an enjoyable week working there, staying in Rruga Brigada VIII in the Blloku area. Lots of nice restaurants and pubs !!

Happy you liked my hometown

Wished Mcgraegor came too

In what way doesn't it work? It seems to be fine to me!! Are you sure that you array entered it? Are the curly brackets there?

I must have done sth wrong, seems perfectly working in your workbook

You added min and max values which is what I planned to do. Perfect


though when A:C range is all 0 I need it to show blank pertaining to this criteria not being met ,IF(A2:C2>2

If it is possible that if max value is blank also min value shows blank

..

A slight addition (array entered) sorts that:

=IF(AND(A2=0,B2=0,C2=0),"",IFERROR(INDEX($A$1:$C$1,MATCH(MAX(IF(E2:G2=0,IF(A2:C2>2,A2:C2))),IF(E2:G2=0,A2:C2),0)),""))

and

=IF(AND(B2=0,C2=0,D2=0),"",IFERROR(INDEX($A$1:$C$1,MATCH(MIN(IF(E2:G2>0,IF(A2:C2>2,A2:C2))),IF(E2:G2>0,A2:C2),0)),""))

But. Take a look at row 2. There are two possible answers. Can this happen in your real data? how do you want it handled?

Mmmm. i think this is it.

Woo Hoo!!!