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 to update the following formula in Excel 2010

Posted on 2014-07-29
9
188 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

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.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

808 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