Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

I need an excel formula

Posted on 2014-03-04
19
262 Views
Last Modified: 2014-03-05
I am needing an excel formula to search with specific variables and return the most recent date out of a list.

I am using the following formula that has almost all of the variables I need, except for the part that with search through the list of dates, and return the most recent.

=IF($Q10="","",SUMPRODUCT(('R:Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000=$Q10)*('R:\Reports\Count\[Count.xlsx]MFDATA'!$M$2:$M$5000="N")*('R:\Reports\Count\[Count.xlsx]MFDATA'!$C$2:$C$5000<>"")*ISNA(MATCH('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000,'R:\Reports\Count\[Count.xlsx]MFINFO'!$D$5:$D$7,0))))

The column with the date in it would be: ('R:\Reports\Count\[Count.xlsx]MFDATA'!$Q$2:$Q$5000

And I know a sumproduct is not what I am needing, I was trying to using an index/match with the MAX function but was having problems with all of the variables.,
0
Comment
Question by:wrt1mea
  • 10
  • 9
19 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39904413
Try:

=IF($Q10="","",MAX(IF(('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000=$Q10)*('R:\Reports\Count\[Count.xlsx]MFDATA'!$M$2:$M$5000="N")*('R:\Reports\Count\[Count.xlsx]MFDATA'!$C$2:$C$5000<>"")*ISNA(MATCH('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000,'R:\Reports\Count\[Count.xlsx]MFINFO'!$D$5:$D$7,0)),'R:\Reports\Count\[Count.xlsx]MFDATA'!$Q$2:$Q$5000
)))

confirmed with CTRL+SHIFT+ENTER not just ENTER
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904484
Yes! This works, but you also exposed a flaw.

I need to make sure that it returns the max value ONLY IF Column Q has values in it. For example, if there were 4 diff dates in column Q, it returns the most recent! GREAT!!

If there are only 1 date out of 4 instances of it matching that in column q:

'R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000=$Q10)

Then, I need it to return blank, until all of the instances have a value...and they have met the other criteria.


Should I open a diff question?
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39904497
So even if 3 out of 4 instances of a match contain a date, then you want a blank?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:wrt1mea
ID: 39904507
yes sir....I am returning it on a prod sheet and dont want to show the last date until the project has dates in every field, thus signifying the project is closed.
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904509
and 4 was an example. could be 1, 2, 5, 18.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39904511
If so, try:

=IF(Q10="","",IF(SUMPRODUCT(('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000=$Q10)*('R:\Reports\Count\[Count.xlsx]MFDATA'!$M$2:$M$5000="N")*('R:\Reports\Count\[Count.xlsx]MFDATA'!$C$2:$C$5000<>"")*ISNA(MATCH('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000,'R:\Reports\Count\[Count.xlsx]MFINFO'!$D$5:$D$7,0))*('R:\Reports\Count\[Count.xlsx]MFDATA'!$Q$2:$Q$5000=""))>0,"",MAX(IF(('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000=$Q10)*('R:\Reports\Count\[Count.xlsx]MFDATA'!$M$2:$M$5000="N")*('R:\Reports\Count\[Count.xlsx]MFDATA'!$C$2:$C$5000<>"")*ISNA(MATCH('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000,'R:\Reports\Count\[Count.xlsx]MFINFO'!$D$5:$D$7,0)),'R:\Reports\Count\[Count.xlsx]MFDATA'!$Q$2:$Q$5000))))

Open in new window


confirmed with CTRL+SHIFT+ENTER not just ENTER
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904546
It' still returning a date for ones that dont have all of the dates in them.

Trying to get something for you to see. Have to obfuscate some data.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39904575
Are the blanks real blanks? Or maybe there is a space it them, or a 0 formatted as blank?

Can you check that too?
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904584
I didnt think about that....sorry! the column q has a formula in it looking at other cells
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39904629
Can you show that formula here?
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904640
Its on the example I provided. column q:

=IF(P2="",IF(J2="",IF(O2="","",O2),J2),P2)
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39904660
Hmmm.  Looks ok, as long as O, J and P are returning spaces, etc.

Give this version a quick test...

=IF(Q10="","",IF(SUMPRODUCT(('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000=$Q10)*('R:\Reports\Count\[Count.xlsx]MFDATA'!$M$2:$M$5000="N")*('R:\Reports\Count\[Count.xlsx]MFDATA'!$C$2:$C$5000<>"")*ISNA(MATCH('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000,'R:\Reports\Count\[Count.xlsx]MFINFO'!$D$5:$D$7,0))*(ISTEXT('R:\Reports\Count\[Count.xlsx]MFDATA'!$Q$2:$Q$5000)+('R:\Reports\Count\[Count.xlsx]MFDATA'!$Q$2:$Q$5000="")))>0,"",MAX(IF(('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000=$Q10)*('R:\Reports\Count\[Count.xlsx]MFDATA'!$M$2:$M$5000="N")*('R:\Reports\Count\[Count.xlsx]MFDATA'!$C$2:$C$5000<>"")*ISNA(MATCH('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000,'R:\Reports\Count\[Count.xlsx]MFINFO'!$D$5:$D$7,0)),'R:\Reports\Count\[Count.xlsx]MFDATA'!$Q$2:$Q$5000))))

Open in new window


If it still is not working, then if you can post a sample workbook with those formulas in place that would be easiest to see what may be the problem.
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904699
Still returning a date for ones that dont have all of the date info completed.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39904731
I would need to see a sample then...  if the original formula worked, then I am not sure what else would not allow this one to work.
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904744
See the attached....
EXECH03-4-14.xlsx
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39904764
I am on my way out... I will look at it later tonight, if that is okay?

I noticed that your formula references B2:B5000 twice.  Is that supposed to be?
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904778
Not a problem at all! I appreciate it. I am getting ready to leave the office for the day.

Yes, I need to reference B2:B550 twice. Once to find matching number codes, and once to exclude a set of number codes from a specific list.

Thanks for all of the help and tenacity.
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39905083
Hi,

Okay, I tried my original formula and it works fine on your sample workbook.

I only removed the filenames but left the sheetnames and references in tact.

I moved the formulas to MFINFO sheet, in A1 copied to A2.  

Formula in A1 references Q10 and A2 references Q11.  I changed the column B values in lower table of MFDATA sheet to 200 so that I didn't have to change the formula to accommodate separate tables.

You will see that I got the expected results.

Please review and see what might be different in your original file.

P.S.

I am confused about your statement

Yes, I need to reference B2:B550 twice. Once to find matching number codes, and once to exclude a set of number codes from a specific list.

If you are looking for a specific criteria (like 100), then that will automatically exclude anything that may be in D2:D7.  I mean the two checks are mutually exclusive....

If that is the case, then you don't need the ISNA(MATCH()) part.
Copy-of-EXECH03-4-14.xlsx
0
 
LVL 1

Author Closing Comment

by:wrt1mea
ID: 39907185
BOOM!!! YOU ARE THE MAN!

Thank you very much for your help and persistence. Works perfectly!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question