LinkBack URL
About LinkBacks
I get a #value error when this is evaluated.. I'm guessing the ARRAY formula cannot work?
Your thoughts
Please Login or Register to view this content.
I get a #value error when this is evaluated.. I'm guessing the ARRAY formula cannot work?
Your thoughtsPlease Login or Register to view this content.
Cell c4 is a dropdown btw.
Are you entering it using ctrl shift enter key combination.. ?
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), dilipandey@gmail.com
Originally Posted by dilipandey
Yes I did, but it evluates witha value error, maybe the issue is a cell reference?
Does row 1 in Final Data have text strings (i.e. column headers). If so start your references at Row 2. The values in the column AE range must be numeric, and all the arrays must be same size.
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.
HI niceguy21,
Would you be able to upload the sample workbook.? thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
Yes, you are correct my friend, this approach has been successful
I now realise the formula relied upon MIN functions as opposed to MAX functions, and it has worked as such, however.. the issue is now that it is evaluates zeros (which are in the column AE)
My corrected version is still producing zeros found in the field:
Your thoughts.Please Login or Register to view this content.
Last edited by niceguy21; 10-19-2012 at 11:45 AM.
Maybe the issue is that some of the values in AE column are blanks and not zeros?
By multiplying the conditions you get zeroes when they aren't satisfied hence MIN will give you zero, try this syntax with multiple IFs for the MIN parts
=MIN(IF('FINAL DATA'!$AE$2:$AE$250000<>0,IF('FINAL DATA'!$J$2:$J$250000=$C$4,IF('FINAL DATA'!$E$2:$E$250000=$E$4,'FINAL DATA'!$AE$2:$AE$250000))))
Audere est facere
Will this work if the cells are not zeros but instead blank?
In a word, Yes, the blanks aren't the problem. Try this:
In A1 to A4 put 4 "x"s, in A5 put a "y" and in B1 to B5 put the numbers 1 to 5 in order
What do you get with this array formula
=MIN((A1:A5="x")*B1:B5)
and what about this one?
=MIN(IF(A1:A5="x",B1:B5))
only one of those give the correct answer....
My earlier reply may have been a little misleading, so just to clarify - you can multiply conditions.....but you should never include the number range in the multiplication (using *), so my earlier version works but so would this one:
=MIN(IF(('FINAL DATA'!$AE$2:$AE$250000<>0,)*('FINAL DATA'!$J$2:$J$250000=$C$4)*('FINAL DATA'!$E$2:$E$250000=$E$4),'FINAL DATA'!$AE$2:$AE$250000))
In a word, Yes, the blanks aren't the problem. Try this:
In A1 to A4 put 4 "x"s, in A5 put a "y" and in B1 to B5 put the numbers 1 to 5 in order
What do you get with this array formula
=MIN((A1:A5="x")*B1:B5)
and what about this one?
=MIN(IF(A1:A5="x",B1:B5))
only one of those give the correct answer....
Thanks for this, I see your reasoning here in this example...Still learning arrays! There are some additions I making to this formula, If I have issues I will be in touch!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks