Conditional Result based on Multiple Columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AnnArborBrian - 09 May 2008 21:23 GMT

I have 3 conditions defined across 3 columns of data, each with 0's or 1's
depending on partnership status of an account. If all three conditions /
columns are 0, then it is a company direct account. I want to consolidate
the date into 1 column (Company Direct, Partnered on Brand Y, Partnered on
Brand X, Partner Direct).

Would someone please suggest a formula for this, I rarely work with Excel
and it is a Friday . . .

Reply to this Message

Reitanos - 09 May 2008 22:06 GMT

I'm not sure what you're trying to get in the result.
I think you're trying to do this:
=IF(SUM(C5:E5)=0,"Company Direct, Partnered on Brand Y, Partnered on
Brand X, Partner Direct","")

But, is the Y and X info coming from somewhere? If so, I'll pretend
that it is stored in F5 and G5 and it would look more like this:
=IF(SUM(C5:E5)=0,"Company Direct, Partnered on "&F5&", Partnered on
"&G5&", Partner Direct","")

On May 9, 4:23 pm, AnnArborBrian
<AnnArborBr...@discussions.microsoft.com> wrote:

> I have 3 conditions defined across 3 columns of data, each with 0's or 1's
> depending on partnership status of an account. If all three conditions /
[quoted text clipped - 4 lines]
> Would someone please suggest a formula for this, I rarely work with Excel
> and it is a Friday . . .

Reply to this Message

AnnArborBrian - 09 May 2008 22:26 GMT

Thanks for the quick response. Let me simplify and clarify this.

I will reduce it to two columns:

Partnered Partner
on Brand X Direct

G4:G750 H4:H750
0 0
1 0
0 1

If both columns are 0 it is a Company Direct Account and I want a statement
in Column I stating "Company Direct"

If column G is 1, then it is a "Partnered on Brand X" Account, and I want a
statement in Column I stating "Partnered" Account.

If column H is 1, then it is a "Patner Direct" Account, and I want a
statement in Column I stating "Partner Direct"

I am hoping this is straight forward, and appreciate your assistance.

> I'm not sure what you're trying to get in the result.
> I think you're trying to do this:
[quoted text clipped - 16 lines]
> > Would someone please suggest a formula for this, I rarely work with Excel
> > and it is a Friday . . .

Reply to this Message

AnnArborBrian - 09 May 2008 22:47 GMT

Also, there are a couple of conditions that are "Mixed" where some business
units on the account are "Partnered", and some are Partner Direct, such that
both columns G and H have 1, and I need to return to Column I the statement
"Mixed"

Reply to this Message

Reitanos - 10 May 2008 01:06 GMT

Allrightythen!
Try this:
=IF(SUM(G4:H4)=0,"Company Direct",IF(G4=1,"Partnered on Brand
X","Partner Direct"))

In English it says if they're both zero write "Company Direct",
otherwise if G4 is 1 then write "Partnered on Brand X" otherwise (ie,
not both zero and not a 1 in G4) write "Partner Direct"
That will work as long as those are the only 3 possibilities.

On May 9, 5:47 pm, AnnArborBrian
<AnnArborBr...@discussions.microsoft.com> wrote:

> Also, there are a couple of conditions that are "Mixed" where some business
> units on the account are "Partnered", and some are Partner Direct, such that
[quoted text clipped - 9 lines]
> > Would someone please suggest a formula for this, I rarely work with Excel
> > and it is a Friday . . .

Reply to this Message

AnnArborBrian - 10 May 2008 02:03 GMT

Thanks, it mostly works, except for the "Mixed" conditions I described
previously (see below). Is there a way that that condition can also be
modeled?

> Allrightythen!
> Try this:
[quoted text clipped - 21 lines]
> > > Would someone please suggest a formula for this, I rarely work with Excel
> > > and it is a Friday . . .

Reply to this Message

AnnArborBrian - 10 May 2008 14:29 GMT

After reviewing in detail my data file, I have the remaining condition to
solve. In some contexts certain companies have individual business units
that are either direct or partnered, constituting a "Mixed" model. The data
for this varies in values (an artifact of the original data source), but
essentially is *not* equal to 1.

> Partnered Partner
> on Brand X Direct
[quoted text clipped - 5 lines]
> 0.99 0
> 0 .54

These conditions are reflected in an existing column (say F) with the
following formula:

=IF(AND(E4<>"",OR(G4<>INT(G4),H4<>INT(H4))),"Mixed"," ")

Column E = Customer Name

Is there a way I can add this to the recommended formula (from Reitanos) to
resolve everything in the new column I, to include the "Mixed" condition?

=IF(SUM(G4:H4)=0,"Company Direct",IF(G4=1,"Partnered on Brand
X","Partner Direct"))

Maybe I am asking the impossible, and I should just transpose the current
"Mixed" occurances to the new column I manually.

Thank you for any and all assistance. There are three files I need to do
this against, for the Americas, EMEA and APAC so it would help not to have to
search manually (each has over 750 lines of data. . . )

Best regards,

Brian

> Allrightythen!
> Try this:
[quoted text clipped - 21 lines]
> > > Would someone please suggest a formula for this, I rarely work with Excel
> > > and it is a Friday . . .

Reply to this Message