Solved MS Excel - Three Conditions for IF Function

August 3, 2012 at 09:50:39
Specs: Windows XP
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!


See More: MS Excel - Three Conditions for IF Function

Report •

#1
August 4, 2012 at 11:58:29
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

http://www.skeptic.com/


Report •

#2
August 4, 2012 at 16:54:48
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 Emails

Reviewer #1                          5                      2               0                 3

Reviewer #2                          1                      0               0                 1

Reviewer #3                          7                      2               0                 4

Reviewer #4                          2                      0               2                 0


Report •

#3
August 5, 2012 at 07:29:43
✔ Best Answer
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          0

In 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

http://www.skeptic.com/


Report •

Related Solutions

#4
August 6, 2012 at 06:20:33
Hi mmcconaghy,

The formula works perfectly!

Thank you for your help!


Report •

Ask Question