M,
Hello again. For these, there are some steps you can walk through in order to discover these formulas for yourself. I'll help guide you with the first formula so you can see the steps breaking down and how you pull it all back together. First, you need to determine what separate logic forks you will need. So first, within a&b, which is fairly simple and can be applied later. Second, i=1, also a fairly intuitive one. Third, before or at the same time, which is a little tricky, but not impossible. And finally, f or h=1, which is also fairly easy.
Based on this, the only difficult part of this is figuring out the third logical fork. Fortunately, we already did something eerily similar in a previous post on this, so let's start there.
Now this formula is checking to see if the row is less than or equal to the first row in the A column grouping that had a 1 in the D column, and returning C2 if so. We only need to make a few minor modifications to make this work for our purposes. And while we're at it, let's take care of the first logic check at the same time, since it works well to do so.
I put the place holders in the formula so we know where to put other formulas later in case we need that. It also helps out tremendously when you are putting together what ends up being a fairly large formula. This is just something I personally do to help myself out, but I recommend putting something you will recognize there so you fill out the entire formula the first time and avoid those annoying Excel errors that usually suggest changing your formula to something completely different than what you intended. We need this again for ensuring that we are before h=1, so it's the exact same formula with two letter changes.
So, that takes care of the last two logic checks, along with the first one. All we need to do now is check the second logic check.
Now it's simply combining things. Because the I2=1 is the basis for everything else, it's a lot easier on your processor to evaluate that first. It may not make a difference in a sheet with 30-50 rows of data, but if you have a sheet with 1000s of rows of data, it can be noticeable. So if I does not equal 1, it returns 0, so that's easy.
Now, you need to add the other part in. We didn't quite finish it, since we didn't include the logic to check either/or in regard to f and h. Logically, you can use the OR function, and change the formulas to check and see if the row is higher than the first row of either f or h, or you can use an AND function and not change the formulas. It's your choice, but I prefer the AND as it makes for less changes in the end.
Now all we have to do is fill in the last true and false values to match what we want and we're on to testing.
Remember with these formulas that use the INDEX function and build arrays that they need to be entered as array formulas (Ctrl+Shift+Enter) to work correctly.
Try going through these steps to come up with your other formulas and let us know if you get stuck. I feel the best way to learn is to do, and figuring it out on your own always feels just a bit more satisfying to me. However, please let us know if you need help with anything and we'd be happy to go through the steps again.
LinkBack URL
About LinkBacks
Register To Reply
.
Originally Posted by FlameRetired
Bookmarks