Help with "Move" Formula Dependant on "IF" Statement

**financeanalyst** · 04-15-2014, 12:13 PM

Hi all,

I'm looking for a bit of help with the following - in the attached spreadsheet, I have data pertaining to the amount of money raised for a bunch private equity funds, and the dates when this money was raised.

For a little background, private equity funds raise money in various batches or "closes", so a fund may initially raise $5M in its "first close" and then in its "second close" it may raise another $5M or so. And then a "final close" would be the last time that fund would raise money.

I'm trying to figure out a way to move data in my "Fund Size" and "Fund Size Date" columns to the corresponding "First/Second/Final" close amount/date columns, according to the "Status" of the fund in my Column A.

Essentially, if a fund is either "Fundraising" or "Fundraising and Investing" status, then I would need the fund sizes and fund size dates to populate in the corresponding columns left to right.

And then if a fund is in "Investing" status, the most recent values (or those furthest right) would need to appear in the "Final Close" columns.

And idea how to go about this?

Greatly appreciate the help, as always!!

**Bernie Deitrick** · 04-15-2014, 02:08 PM

Repost with a copy of that first sheet modified to show what you want and where you want it. Include both sheet versions in your new file.

**financeanalyst** · 04-15-2014, 03:09 PM

Here's an updated version of my sheet with examples of how I'm trying to reformat the data.

Essentially, if a fund status is "Investing" than the most recent values for fund size/date should go in the "Final" fund size/date columns and earlier values left to right in the First, Second, Third fields depending on how many values there are.

If the fund is in "Investing" status and there is only one value for fund size/date, than those should go right to the "Final" columns.

And then if a fund is in Pre-Marketing, Fundraising, or Fundraising & Investing status, than the sizes and dates should go chronologically right to left in the fund size and fund date fields.

**Bernie Deitrick** · 04-15-2014, 03:14 PM

Where did the dates come from, and why is there no third close in row 4? And are your dates MM/DD/YYYY or DD/MM/YYYY?

**financeanalyst** · 04-15-2014, 03:19 PM

The dates are coming from an export of data into Excel, which is why they are spaced out with the "|" by default.

In row 4, there's no third close because the fund is "Investing" and no longer raising money - so the third amounts are therefore the Final closes. If it was in Pre-marketing, Fundraising, or Fundraising & Investing statuses, then those would be third closes.

Hope that helps to clarify!

**financeanalyst** · 04-15-2014, 03:29 PM

The dates are coming from an export of data into Excel, which is why they are spaced out with the "|" by default.

In row 4, there's no third close because the fund is "Investing" and no longer raising money - so the third amounts are therefore the Final closes. If it was in Pre-marketing, Fundraising, or Fundraising & Investing statuses, then those would be third closes.

Hope that helps to clarify!

Help with "Move" Formula Dependant on "IF" Statement

LinkBack

Thread Tools

Rate This Thread

Display

Help with "Move" Formula Dependant on "IF" Statement

Re: Help with "Move" Formula Dependant on "IF" Statement

Re: Help with "Move" Formula Dependant on "IF" Statement

Re: Help with "Move" Formula Dependant on "IF" Statement

Re: Help with "Move" Formula Dependant on "IF" Statement

Re: Help with "Move" Formula Dependant on "IF" Statement

Thread Information

Users Browsing this Thread

Similar Threads

Don't know the Forumlas with problem either using "vlookup" "if" "and" or "index" "match"

[SOLVED] How to USE """"" cells count """"" change font color

[SOLVED] If there is any text in column "A$" on "sheet1" then move cell to column "A$" on "sheet2"

Formula returning result of "True" or "False" instead of "1" or "0"

if statement see "Male" or "Female" returning "Y" otherwise leave blank

Bookmarks

Bookmarks

Posting Permissions