Question description:
This user has given permission to use the problem statement for this
blog.
I am having issues with max If function array formula using Is number / Find to find data
Solved by D. C. in 22 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
06/10/2017 - 10:50
Hi there. I understand that you need help with the Max IF array formula. Are you able to upload your data and your existing formula so I can analyze it?
User
06/10/2017 - 10:50
hello
User
06/10/2017 - 10:50
i have a file
User
06/10/2017 - 10:50
can i upload
Excelchat Expert
06/10/2017 - 10:51
Sure. Please upload using the paper clip icon next to this chat.
User
06/10/2017 - 10:51
i have xlsm file
User
06/10/2017 - 10:51
it doesnt seem to be accepted
User
06/10/2017 - 10:51
upload format
Excelchat Expert
06/10/2017 - 10:51
I see. Please upload in Google Drive instead.
User
06/10/2017 - 10:52
which button do i press for that
Excelchat Expert
06/10/2017 - 10:52
Please go to https://drive.google.com Click New > File Upload. Navigate to the file and select it to upload. Click Recent. Right click on the file. Click Share. Click Get Shareable Link. Make sure "Anyone with the link can view" is selected. Click Copy Link. Then paste the link here so I can download it.
Excelchat Expert
06/10/2017 - 10:52
Please be advised that we have a policy of 1 question per session and for this session we will be working on your Max If array formula function.
User
06/10/2017 - 10:52
ok sure
User
06/10/2017 - 10:54
https://drive.google.com/open?id=0B4WvKmxpOE2iR2VpSk1jZjFKT0E
User
06/10/2017 - 10:54
cell L28
User
06/10/2017 - 10:55
sheet 3BAI-20
Excelchat Expert
06/10/2017 - 10:55
Which sheet.
Excelchat Expert
06/10/2017 - 10:55
Okay, do you have named ranges here?
User
06/10/2017 - 10:56
yes
User
06/10/2017 - 10:56
let me reload file
Excelchat Expert
06/10/2017 - 10:56
Okay, that can complicate it a bit as we'll have to follow them too.
User
06/10/2017 - 10:56
P6 data page was slight wrong
User
06/10/2017 - 10:56
i manually changed something
Excelchat Expert
06/10/2017 - 10:56
Okay, sure. Please note that we have a limited amount of time for this and we have to act quickly soon.
User
06/10/2017 - 10:57
https://drive.google.com/open?id=0B4WvKmxpOE2iell4VWtJNjFNVlE
User
06/10/2017 - 10:57
ok
User
06/10/2017 - 10:57
ready
User
06/10/2017 - 10:57
3BAI-20 L28
User
06/10/2017 - 10:58
If min array formula should return 3/10/17
User
06/10/2017 - 10:58
based on Min IF
User
06/10/2017 - 10:59
it doesnt seem to like numbers at end
Excelchat Expert
06/10/2017 - 10:59
Did you hide the first columns in p6data?
Excelchat Expert
06/10/2017 - 10:59
Because I'm trying to follow your P6_SamID
User
06/10/2017 - 11:00
freeze panes
Excelchat Expert
06/10/2017 - 11:00
P6_SamID = A:A
User
06/10/2017 - 11:00
yes
User
06/10/2017 - 11:01
problem is if i change P6_DAGlobal name range to "DA" it works
User
06/10/2017 - 11:01
if I put DA01
User
06/10/2017 - 11:01
DA02 doesnt work
User
06/10/2017 - 11:01
I want it work like a wildcard
User
06/10/2017 - 11:01
thats why I am using the ISNUMBER(FIND) function
Excelchat Expert
06/10/2017 - 11:01
I need to understand how your formula works first before I can give you my recommendations. I'll need time to disect it as the named ranges aren't very clear.
User
06/10/2017 - 11:02
ok thanks
Excelchat Expert
06/10/2017 - 11:02
Please give me a few minutes.
Excelchat Expert
06/10/2017 - 11:02
P6_AccuralNC = AG:AG
User
06/10/2017 - 11:03
basically there is 3 criteria
User
06/10/2017 - 11:03
IF(P6_SamID=$D$6,IF(P6_AccuralNC=$B28,IF(ISNUMBER(FIND($A28,P6_DAGlobal))
User
06/10/2017 - 11:03
only one not working is last one
User
06/10/2017 - 11:03
IF(ISNUMBER(FIND($A28,P6_DAGlobal))
Excelchat Expert
06/10/2017 - 11:04
So basically, it has to meet all 3 criteria.
User
06/10/2017 - 11:04
yes
Excelchat Expert
06/10/2017 - 11:04
And then return the minimum.
User
06/10/2017 - 11:04
yes
User
06/10/2017 - 11:04
but it doesnt like that i got DA01 DA02 etc
User
06/10/2017 - 11:05
I want A28 to work as wildcard
Excelchat Expert
06/10/2017 - 11:05
What's the expected value of L28
User
06/10/2017 - 11:05
thats why i am using the find function
User
06/10/2017 - 11:05
3/10/17
User
06/10/2017 - 11:05
3/10/17
User
06/10/2017 - 11:05
O36 P6 Data
User
06/10/2017 - 11:05
is correct MIN value
Excelchat Expert
06/10/2017 - 11:06
I see. One moment.
Excelchat Expert
06/10/2017 - 11:06
If you are prompted to extend, please extend the session.
User
06/10/2017 - 11:06
ok
Excelchat Expert
06/10/2017 - 11:09
Okay, I have to rewrite your array formula:
Excelchat Expert
06/10/2017 - 11:09
Please try this:
Excelchat Expert
06/10/2017 - 11:09
=MIN(IF((P6_SamID=$D$6)*(P6_AccuralNC=$B28)*(ISNUMBER(FIND($A28,P6_DAGlobal))),VALUE(LEFT(P6_Start,9))))
User
06/10/2017 - 11:10
ok perfect seems to be working
User
06/10/2017 - 11:10
how does that work vs mine not working
Excelchat Expert
06/10/2017 - 11:11
Basically, I just use your same formula, the only difference is the formatting. Yours may be calculating it in an incorrect order.
User
06/10/2017 - 11:11
u are using the *
User
06/10/2017 - 11:11
thats the key difference
Excelchat Expert
06/10/2017 - 11:11
I wasn't really able to fully analyze it as I'm not familiar with how you constructed your array formulas.
User
06/10/2017 - 11:11
ok great
User
06/10/2017 - 11:11
regardless working
User
06/10/2017 - 11:11
can u explain how i can reach you on here in future
User
06/10/2017 - 11:11
for other excel help
Excelchat Expert
06/10/2017 - 11:12
I'm afraid you will just have to post your question and the right expert will pick you up.
User
06/10/2017 - 11:12
ok
User
06/10/2017 - 11:12
thanks
Excelchat Expert
06/10/2017 - 11:12
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating if you think I deserve it. :)
Excelchat Expert
06/10/2017 - 11:12
Please do not forget to click the End Session otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
