Can you please help me create a formula for the following conditions: I have 4 columns: Column D - Total Reviews, Column J - Reviews Which Came for Campaign ID 1, Column K - Reviews Which Came for Campaign ID 2, Column L - Reviews Which Came for Campaign ID 3. The rows in the spreadsheet represent content contributors. In Column M I need to enter which of the contributors came from Email, which are Organic and which are Hybrid (combination of Email and Organic).

I need to create a formula to label each kind of the contributor in column M.

Here is the formulas I created for each kind of contributor and then I combined the formulas. Unfortunately it doesn't work and gives me #Value! error.

Organic: If J2=0, K2=0, L2=0, then return "Organic"

IF(AND(J2=0, K2=0, L2=0), "Organic")Email: If at least one out of three cells (J2, K2, or L2) is > 0, then return "Email"

IF(OR(AND(J2>0), AND(K2>0), AND(L2>0)), "Email")Hybrid: A) If D2 > than sum of J2+K2+L2 and the sum of J2+K2+L2 >=1 then return "Hybrid". It can be written as: B) If D2 > than sum of J2+K2+L2 and the sum of J2+K2+L2 not equal zero, then return "Hybrid". I don't know how to insert 'not equal zero, so I went with A) option.

IF(AND(D2>(J2+K2+L2), (J2+K2+L2)>=1), "Hybrid")Then I combined these three conditions into one formula, which is obviously incorrect because it doesn't work:

=IF(AND(J2=0, K2=0, L2=0), "Organic"), IF(OR(AND(J2>0), AND(K2>0), AND(L2>0)), "Email"), IF(AND(D2>(J2+K2+L2), (J2+K2+L2)>=1), "Hybrid")

I would really appreciate any help to correct this formula!

What is the data you are entering in your cells? In other words, what piece of data represents Organic,

what represents E-Mail?Is it 0 = Organic & 1 = E-Mail?

If someone is manually entering the data, why not simply have a

third choice of 2 = Combo?Post a short example of your data, but first read this How-To

and see how to post data in this forum:http://www.computing.net/howtos/sho...

MIKE

Thank you so much for your quick reply to my post!!!! The numbers represent the quantity of reviews. Total reviews = Organic Reviews + Reviews which came from retail customers when they were sent post-purchase review solicitation emails + Reviews which came from dotcom customers when they were sent post-purchase review solicitation emails + Reviews which came from thank you page in the review solicitation emails.

Note that there is no 'Organic Reviews' column. Organic reviews can be calculated by deducting the sum of the reviews from 3 sources from total. For example: Reviewer #3 submitted only 1 organic review = 7- (2+0+4).

Review Contributor ID Total Reviews (Organic & Email) Retail Emails Dotcom Emails Thank you EmailsReviewer #1 5 2 0 3Reviewer #2 1 0 0 1Reviewer #3 7 2 0 4Reviewer #4 2 0 2 0

OK, thanks for the example, Most of your formulas were pretty close, I just spruced them up a bit.

I think this will get you what you want:

If your data looks like:

A D J K L 1 ID Total Reviews Retail Dotcom Thank you 2) Reviewer #1 5 2 0 3 3) Reviewer #2 1 0 0 1 4) Reviewer #3 7 2 0 4 5) Reviewer #4 2 0 2 0In cell M2 enter the formula:

=IF(AND(SUM(J2:L2)>1,D2>SUM(J2:L2)),"Hybrid",IF(OR(J2>0,K2>0,L2>0),"Email",IF(AND(J2=0,K2=0,L2=0), "Organic","")))

It is rather long, so copy from this post and paste it into your spreadsheet.

See how that works.

MIKE

Hi mmcconaghy, The formula works perfectly!

Thank you for your help!

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History