Solved

I need to update the following formula in Excel 2010

Posted on 2014-07-29
9
187 Views
Last Modified: 2014-07-29
I am trying to update a formula that I got working with some help from Exp Exchange yesterday. I am not sure if we can do it, but I wanted to give it a try. See the attached example.

I need to update the following formula:

=IF($G200="M",IFERROR(INDEX('[Report 7-29-14.xlsx]Projects'!$G$10:$G$150,MATCH($A10,'[Report 7-29-14.xlsx]Projects'!$A$10:$A$150,0))&"",""),IF(I10="","",TEXT(I10,"M/D/YY")&" (E)"))

The formula returns values in Column G.

I need it to, after it returns values, it looks at Column V. If the info in Column V is the same, V12=V13=V14, it looks back at Column G, finds the earliest date, 6/21/15 (E), and returns that date for the other matching cells.

I know this is confusing. I am trying to return the earliest date in Column G for all of the matches in Column V.
EXP-EXCH-7-29-14.xlsx
0
Comment
Question by:wrt1mea
  • 4
  • 4
9 Comments
 
LVL 18

Assisted Solution

by:krishnakrkc
krishnakrkc earned 100 total points
ID: 40227005
Hi

Try this array formula.

=IF(COUNTIF($V$10:V10,V10)=1,MIN(IF($V$10:$V$40=V10,LEFT($G$10:$G$40,FIND(" ",$G$10:$G$40&" ")-1)+0)),"")

Kris
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40227101
This is confusing, because your example formula appears to be self-referential, that is, it is checking values IN column G for the occurrence of "M", but the values you have in column G now look like the results of this formula.  

And while Kris' array formula (entered with [Ctrl]+[Shift]+[Enter) is absolutely correct in returning the earliest (minimum) date in column G for any given value in column V, you don't specify where this formula needs to go or if it is somehow supposed to be incorporated into the example formula.

Please clarify these issues and I'm sure we'll get an acceptable answer to you.

Regards,
-Glenn
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 40227368
OK. Here is what I am trying to do. Maybe I need to reference a different column....

The formula that I am using to return dates in Column G works perfectly in my production sheet. If it finds an M, it returns a previous weeks report date information, if it does not, it returns a new date based on the last part of the formula. I know you probably know how to read a formula, just was saying.

I would love for the results after going through my first formula, go through the second formula that Kris posted and display the results in Column G. So yes, incorporating the formula that Kris posted into my formula would be ideal. Like I mentioned previously, if I need to reference another column, Like Column AZ to return info first, then run the formula in column G to display the new results, I am fine with that.

Hope this helps
0
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40227439
So, am I correct in stating that you want the formula now to display the earliest completion date in column G for any given area name?   See this:
example update
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 40227446
YES!!! Looks good!
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 40227466
But I am wanting the formula to display the earliest completion date, after the original formula I posted runs and evaluates the data to return.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 400 total points
ID: 40227563
I presumed that you wanted the original algorithm (formula) to return a date - either from the external Projects sheet, or from column I - but then only display the earliest for any given Area Name.

The only way to do this is by converting the existing Completion Date formulas into a "helper" column and displaying a new Completion Date column.  This would essentially make Kris' solution the new displayed result (you could hide/move the existing set of formulas).

I've created an example and have modified Kris' formula somewhat to always show the earliest completion date (not just at the start of each Area Name group).  The original formulas are in column AA, but could be anywhere, even on another sheet.

Regards,
-Glenn
EE-EXP-EXCH-7-29-14.xlsx
0
 
LVL 1

Author Closing Comment

by:wrt1mea
ID: 40227707
AWESOME!!!!!!!!!!!!!!!

THANK YOU VERY MUCH! GONNA SAVE A TREMENDOUS AMOUNT OF TIME!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40227711
YEA!  I'm glad we could help.

-Glenn
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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