• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to reduce lot of "if's"

V

vaibhav tandon

Member
Hi Team,

I am trying to create a value based on set of 4 cell values but it requires a lot of 'if', could anyone please help in reducing if's or help in creating a better formula to solve the below issue.

In the attached test file I need a value in column 'M' based on values in column C, D, J and K. The values are given in table below from row 9 to 24.

For eg if a resource's resource type is 'Dev' , Sprint Type is 'Dev', Issue type is 'Story' and Workstream is 'SWP' then the expected value should be '
SWPDev'. Similarly the same values should be based on information from row 10 - 24.

Hope there is a better solution to what I am using.

Please let me know in case of any queries.

Thanks
Vaibhav
 

Attachments

Or try this multi-conditional Lookup formula.

In M3, copy down :

=IFERROR(LOOKUP(2,1/((A$10:A$25=J3)*(B$10:B$25=K3)*(C$10:C$25=C3)*(D$10:D$25=L3)),J$10:J$25),"")

Regards
Bosco
 
vaibhav tandon said:
Hi Team,

I am trying to create a value based on set of 4 cell values but it requires a lot of 'if', could anyone please help in reducing if's or help in creating a better formula to solve the below issue.

In the attached test file I need a value in column 'M' based on values in column C, D, J and K. The values are given in table below from row 9 to 24.

For eg if a resource's resource type is 'Dev' , Sprint Type is 'Dev', Issue type is 'Story' and Workstream is 'SWP' then the expected value should be '
SWPDev'. Similarly the same values should be based on information from row 10 - 24.

Hope there is a better solution to what I am using.

Please let me know in case of any queries.

Thanks
Vaibhav
Click to expand...
You are correct: that is a lot of "if"s in your formula. A LOT!

As you undoubtedly know, Excel has many different functions you can use to get to the same result. It's just a matter of using what you know how to do and what you like using like the best.

I don't like to use array formulas unless I have to, because they can really bog down a spreadsheet if you have a long list of data the array has to analyze.

bosco's formula works to, although I couldn't really tell you why (I'd need at least an hour to understand how the logic and syntax works) :)

I like to use the simplest, least complicated method possible. (See attached)

In column M I used a table that I made on the "Expected Answers" Tab. Using a table is very easy intuitive. I always name tables whenever I can, because it makes using and auditing formulas very easy. To get the Expected Answer, I used vlookup.

However, after looking at the logic in all of your if(and()) statements in the original file, I noticed a trend. The expected answer is simply the name of the Workstream in Column L and if either of the Resource Type or the Sprint Type were a Test then "test" was added onto the Workstream name, or if both the Resource and Sprint Types were Dev then "Dev" was added onto the Workstream name. So that makes coming up with the Expected answer even easier. A simple if(or() statement does the trick, and you don't even need to use a table. I used that in Column N (see below).

=L3&IF(OR(J3="Test",K3="Test"),"Test","Dev")
 

Attachments

You must log in or register to reply here.
Back
Top