Solved

Need help with complex formula in excel

Posted on 2014-09-30
13
49 Views
Last Modified: 2014-10-27
If column D = Defend
AND column E or G greater than or equal to 5,000,000
THEN set column N to 1
 
If column D = Renewal or Upsell or New Business
AND column E or G greater than or equal to 10,000,000
THEN set column N to 1
 
If column D = Renewal or Run Rate
AND column E or G less than 10,000,000
THEN set column N to 2

If column D = Up Sell or New Business
AND column E or G less than 10,000,000
THEN set column N to 4

If column D = Run Rate
THEN set column N to 2

If column D = New Business
AND column E or G equal to or greater than 10,000,000
THEN set column N to 1
0
Comment
Question by:Matt Pinkston
  • 4
  • 2
  • 2
  • +4
13 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40353257
assuming your data starts from the second row .  put this formula in N2 and copy down
=IF(AND(D2="Defend",OR(E2>=5000000,G2>=5000000)),1,IF(AND(D2="Renewal or Upsell or New Business",OR(E2>=10000000,G2>=10000000)),1,IF(AND(D2="Renewal or Run Rate",OR(E2<=10000000,G2<=10000000)),2,IF(AND(D2="Up Sell or New Business",OR(E2<=10000000,G2<=10000000)),4,IF(D2="Run Rate",2,IF(AND(D2="New Business",OR(E2>=10000000,G2>=10000000)),1,""))))))

Open in new window

0
 
LVL 7

Expert Comment

by:Robby Swartenbroekx
ID: 40353274
=IF(D1="Run Rate";2;IF(AND(D1="Defend";OR(E1>=5000000;G1>=5000000));1;IF(OR(D1="Upsell";D1="New Business");IF(OR(E1>=10000000;G1>=10000000);1;4);IF(D1="Renewal";IF(OR(E1>=10000000;G1>=10000000);1;2);""))))

Open in new window


If the Value in D1 isn't one of the above, or it is Defend with a value less then 5 Million in E and G column the value of N1 is empty
0
 
LVL 18

Expert Comment

by:Raheman M. Abdul
ID: 40353275
Considering the order of processing from top to botton in your question I have formulated the below:

Enter this formula in column N

=IF(AND(D1="Defend",OR(E1>=5000000,G1>=5000000)),1,IF(AND(OR(D1="Renewal",D1="Upsell",D1="New Business"),OR(E1>=10000000,G1>=10000000)),1,IF(AND(OR(D1="Renewal",D1="Run Rate"),OR(E1<10000000,G1<10000000)),2,IF(AND(OR(D1="Up Sell",D1="New Business"),OR(E1<10000000,G1<10000000)),4,IF(D1="Run Rate",2,IF(AND(D1="New Business",OR(E1>=10000000,G1>=10000000)),1,""))))))
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40353299
I do not understand it.  when a question is answered, why others are stepping on others toes.

right after the question asked, it has been answered by me.  so, either you spent your time re-inventing the wheel, or simply copying my formula and re pasting it here.
=IF(AND(D1="Defend",OR(E1>=5000000,G1>=5000000)),1,IF(AND(OR(D1="Renewal",D1="Upsell",D1="New Business"),OR(E1>=10000000,G1>=10000000)),1,IF(AND(OR(D1="Renewal",D1="Run Rate"),OR(E1<10000000,G1<10000000)),2,IF(AND(OR(D1="Up Sell",D1="New Business"),OR(E2<=10000000,G2<=10000000)),4,IF(D2="Run Rate",2,IF(AND(D2="New Business",OR(E2>=10000000,G2>=10000000)),1,""))))))
0
 
LVL 7

Expert Comment

by:Robby Swartenbroekx
ID: 40353334
- I had the question open and started writing on it, I've never seen your code before I posted my code.
- Raheman and myself also read the question a little bit different than you. we all are correct. Depending on which of the 2 interpretations is correct, that means that or your code is good, of Raheman and mine are good.
- I also looked for impossible combinations and better sorting. If my interpretation is correct, then the last part is already dealth with by part 2, part 5 simpifies part 3 and I also combined part 2 and 4 to make the formula as small as possible.

It also took a little bit longer with my because my Excel is in Dutch, so I hade to write & test my code + translate it to English.
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 40353340
Try this:

=IF(D4="New Business", 2, IF(AND(D4="Defend", OR(E4>= 5000000, G4>=5000000)), 1, IF(AND(OR(D4="Renewal", D4="Upsell", D4="New Business"), OR(E4>=10000000, G4>=10000000)), 1, IF(AND(OR(D4="Renewal", D4="Run Date"), OR(E4<=10000000, G4<=10000000)), 2, IF(AND(OR(D4="Up Sell", D4="New Business"), OR(E4<=10000000, G4<=10000000)),4,IF(AND(D4="New Business"), OR(E4>=10000000, G4>=10000000), 1 ))))))
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Expert Comment

by:Raheman M. Abdul
ID: 40353452
Professorjimjam
I never saw your code before I post after I finish in my excel sheet. I opened the question and started working on it and just posted my comment.
I saw your code now and mine is different than you.
Don't worry mate.

Robby, thanks mate
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40353479
it is alright.    no big deal.

I understand
0
 

Author Comment

by:Matt Pinkston
ID: 40353481
okay whose on first I need to do some playing to see whose "first" response answered the data call.
0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40353487
pinkstonmp

first response was mine

my code was :)  

=IF(AND(D1="Defend",OR(E1>=5000000,G1>=5000000)),1,IF(AND(OR(D1="Renewal",D1="Upsell",D1="New Business"),OR(E1>=10000000,G1>=10000000)),1,IF(AND(OR(D1="Renewal",D1="Run Rate"),OR(E1<10000000,G1<10000000)),2,IF(AND(OR(D1="Up Sell",D1="New Business"),OR(E2<=10000000,G2<=10000000)),4,IF(D2="Run Rate",2,IF(AND(D2="New Business",OR(E2>=10000000,G2>=10000000)),1,"")))))) 

Open in new window

0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40353522
As a side note to previous experts, may I recommend that you refresh the browser before posting an answer?  That will ensure that you can see if anyone else has supplied an acceptible answer before you.  If you think you have a better solution (more efficient, simpler, easier-to-understand, more-flexible, etc.) then there's no reason not to post.  

It happens to all of us; some folks are quick on the draw, but shoot and miss; some wait for the first shot and then get ya right between the eyes.  :-)

-Glenn
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40354306
To throw another option into the fray, all of the above could possibly be simplified by using a small INDEX table.

Match the Text down a column, match the value across the top, return the value at the intersection.

Thanks
Rob H
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40354326
See attached, change values in cells A11 & A12 and see result in yellow highlighted cell.

Thanks
Rob H
Q-28528650.xlsx
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Merging-Splitting-Multiple-Rows 33 46
Error in formula not increasing value 2 15
Excel 2010 - Comparison of texts in a cell 14 40
Macro Lookup 33 20
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now