Matt Pinkston
asked on
Need help with complex formula in excel
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
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
=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);""))))
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
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,G 1>=5000000 )),1,IF(AN D(OR(D1="R enewal",D1 ="Upsell", D1="New Business"),OR(E1>=10000000 ,G1>=10000 000)),1,IF (AND(OR(D1 ="Renewal" ,D1="Run Rate"),OR(E1<10000000,G1<1 0000000)), 2,IF(AND(O R(D1="Up Sell",D1="New Business"),OR(E1<10000000, G1<1000000 0)),4,IF(D 1="Run Rate",2,IF(AND(D1="New Business",OR(E1>=10000000, G1>=100000 00)),1,"") )))))
Enter this formula in column N
=IF(AND(D1="Defend",OR(E1>
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,G 1>=5000000 )),1,IF(AN D(OR(D1="R enewal",D1 ="Upsell", D1="New Business"),OR(E1>=10000000 ,G1>=10000 000)),1,IF (AND(OR(D1 ="Renewal" ,D1="Run Rate"),OR(E1<10000000,G1<1 0000000)), 2,IF(AND(O R(D1="Up Sell",D1="New Business"),OR(E2<=10000000 ,G2<=10000 000)),4,IF (D2="Run Rate",2,IF(AND(D2="New Business",OR(E2>=10000000, G2>=100000 00)),1,"") )))))
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>
- 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.
- 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.
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 ))))))
=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
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
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
it is alright. no big deal.
I understand
I understand
ASKER
okay whose on first I need to do some playing to see whose "first" response answered the data call.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
Match the Text down a column, match the value across the top, return the value at the intersection.
Thanks
Rob H
See attached, change values in cells A11 & A12 and see result in yellow highlighted cell.
Thanks
Rob H
Q-28528650.xlsx
Thanks
Rob H
Q-28528650.xlsx
Open in new window