Forum Discussion
Multiple logic test formula
If I followed all that you need, the attached file is an example of what would work. Essentially it's a two-dimensional table, with the different routes on the vertical dimension, the different activities on the horizontal, and the corresponding mileages in the interstices of those dimensions.
After you select the route and the activity, a "simple" INDEX and MATCH formula retrieves the mileage.
=INDEX(E5:G13,MATCH(C17,D5:D13,0),MATCH(C18,E4:G4,0))
I’ll give this a go. I ‘think’ I got it to work using IF with nested AND but my formula would be quite long and tedious to write (9 routes X3 possible activities per route = 27 logical tests). Your solution seems much simpler and easier.
Yes, covering multiple possible combinations in a deeply "nested" IF formula can indeed work, but it is awfully difficult to create in the first place, harder still for another person to understand, and next to impossible to maintain (as routes change, for example).
Using a table (or array of those 27 combinations) and one of the many forms of retrieval of information FROM a table achieves the same objectives and is far more readily understood. And as things change--routes get added or deleted, routes get expanded or contracted--it's simple to add other rows, change the numbers in them, and the same formula still works.
If you're not fully familiar with INDEX and MATCH, here's a good reference. You'll also find helpful videos on YouTube.
Thanks again for the help!
You're more than welcome.
- The ability to change routes was a concern I had. Using the INDEX and MATCH command almost effortlessly facilitates changes. I am a novice at Excel, self learning as I go. One thing I have discovered is there are several ways to skin a cat so to speak. It will take me some time to learn the different strategies. I’ve barely scratched the surface and am grateful for folks like yourself that take the time to share their expertise.
I am a novice at Excel, self learning as I go. One thing I have discovered is there are several ways to skin a cat so to speak. It will take me some time to learn the different strategies.
Self-taught is a good thing; it's basically how I learned the ins and outs of spreadsheets. I had the advantage of doing so back in the '80s and '90s when Lotus and then Microsoft issued their software on discs accompanied with printed manuals. I would read the manuals periodically, from start to finish; inevitably noticed what you comment on--that there were multiple ways to get from point A to point B.
I would in particular encourage you to learn to think and design with tables or datasets at the heart of your design. Excel excels in various methods of slicing and dicing, summarizing salient data, from a transactional database. For example, if you were to ultimately want to know how many times per month each route was salted, plowed, or both, a simple daily record of each route and activity could be easily processed by means of the Pivot Table.....and Excel would be doing ALL of the hard work.
I’ve barely scratched the surface and am grateful for folks like yourself that take the time to share their expertise.
I know (virtually) some of the other folks who answer questions here in the forum; I think we all voluntarily do this because we enjoy helping others, appreciating folks like you who also want to learn, and--I know this is true for me--I keep learning more by taking the time to answer a question like yours,