Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Result based on Multiple Columns
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 . . . |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Result based on Multiple Columns
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 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 / 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 . . . |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Result based on Multiple Columns
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. "Reitanos" wrote: 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 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 / 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 . . . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Result based on Multiple Columns
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" "AnnArborBrian" 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 / 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 . . . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Result based on Multiple Columns
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 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 both columns G and H have 1, and I need to return to Column I the statement "Mixed" "AnnArborBrian" 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 / 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 . . . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Result based on Multiple Columns
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? "Reitanos" wrote: 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 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 both columns G and H have 1, and I need to return to Column I the statement "Mixed" "AnnArborBrian" 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 / 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 . . . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Result based on Multiple Columns
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 G4:G750 H4:H750 0 0 1 0 0 1 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 "Reitanos" wrote: 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 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 both columns G and H have 1, and I need to return to Column I the statement "Mixed" "AnnArborBrian" 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 / 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup for a value in multiple columns and return a result | Excel Discussion (Misc queries) | |||
Conditional Format based on forumula result | Excel Discussion (Misc queries) | |||
how can I have a formula result based on multiple criteria/columns | New Users to Excel | |||
Automatically resize columns based on new formula result | Excel Discussion (Misc queries) | |||
conditional formatting based on another cells formula result | Excel Discussion (Misc queries) |