Alternative to Nested IF Statement

**s1crock** · 06-05-2011, 04:22 PM

Hi There,

I'm looking for an alternative to a nested IF Statement. I work for a non-profit organization and we run several daycares. The spreadsheet I need to complete is to calculate kilometers between each daycare... we have 8 day cares. The way I have it set up is that each daycare is assigned a number. So if daycare number 1 travels to daycare number 3 the result is the calculated kilometers. The problem is, is that I have more that 64 nested IF statements in my calculation and ideally I'm looking for an alternative that will allow for MORE results, as we also run several family resource centres and I'd like to add them into the mix.

Does anyone have any suggestions??

My code currently looks like this:

=IF(AND(C8=1,D8=2),1,IF(AND(C8=1,D8=4),24,IF(AND(C8=1,D8=6),17,IF(AND(C8=1,D8=7),22,IF(AND(C8=1,D8=12),75,IF(AND(C8=1,D8=14),1,IF(AND(C8=1,D8=15),27,IF(AND(C8=1,D8=18),21,IF(AND(C8=2,D8=7),22,IF(AND(C8=2,D8=1),1,IF(AND(C8=2,D8=14),1,IF(AND(C8=2,D8=4),24,IF(AND(C8=2,D8=6),17,IF(AND(C8=2,D8=12),74,IF(AND(C8=2,D8=15),27,IF(AND(C8=2,D8=18),21,IF(AND(C8=4,D8=7),4,IF(AND(C8=4,D8=1),24,IF(AND(C8=4,D8=15),4,IF(AND(C8=4,D8=2),24,IF(AND(C8=4,D8=6),13,IF(AND(C8=4,D8=12),77,IF(AND(C8=4,D8=14),23,IF(AND(C8=4,D8=18),57,IF(AND(C8=6,D8=7),15,IF(AND(C8=6,D8=1),17,4,IF(AND(C8=6,D8=15),17,IF(AND(C8=6,D8=2),27,IF(AND(C8=6,D8=12),89,IF(AND(C8=6,D8=14),16,IF(AND(C8=6,D8=18),32,IF(AND(C8=6,D8=4),13,IF(AND(C8=12,D8=16),29,IF(AND(C8=12,D8=7),73,IF(AND(C8=12,D8=20),30,IF(AND(C8=12,D8=1),75,IF(AND(C8=12,D8=2),74,IF(AND(C8=12,D8=14),75,IF(AND(C8=12,D8=18),59,IF(AND(C8=14,D8=1),1,IF(AND(C8=14,D8=2),0,IF(AND(C8=14,D8=7),22,IF(AND(C8=14,D8=4),23,IF(AND(C8=14,D8=6),16,IF(AND(C8=14,D8=15),27,IF(AND(C8=14,D8=18),21,IF(AND(C8=14,D8=12),75,IF(AND(C8=15,D8=1),27,IF(AND(C8=15,D8=7),8,IF(AND(C8=15,D8=4),4,IF(AND(C8=15,D8=2),27,IF(AND(C8=15,D8=6),17,IF(AND(C8=15,D8=14),27,IF(AND(C8=15,D8=18),25,IF(AND(C8=15,D8=12),81,IF(AND(C8=18,D8=1),21,IF(AND(C8=18,D8=2),21,IF(AND(C8=18,D8=7),18,,IF(AND(C8=18,D8=12),59,IF(AND(C8=18,D8=14),21,)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

SEE WHAT I MEAN... I need something more similar!!

Thank you

**MarvinP** · 06-05-2011, 06:36 PM

Hi s1crock and welcome to the forum.

I've built a sample lookup table that uses route names to return distances. I'm not sure but this may be a method to not have to use your nested if statement.

See attached.

BTW - it is much easier to understand your problem if you supply a sample workbook with what is given and what you expect for a result.

**s1crock** · 06-05-2011, 08:16 PM

Thank you MarvinP. I understand exactly how you did that. It was precisely what I was looking for. You Rock!!!

**anchawki** · 06-16-2014, 06:06 PM

Hello
I was browsing the internet and I fell upon your post and it related to my current situation. My sister currently owns three daycares and I am coming in part time from the school board to assist. I am in trying to put together a spreadsheet that can be used to track tuition among other things. Just based on your post I wanted to know if you knew of any templates that would be helpful in my strides to upgrade our three daycares from loads of paper to technology tracking. Thank you!

**Richard Buttrey** · 06-16-2014, 06:13 PM

Hi, and welcome to the forum. Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.