samedi 14 avril 2018

Excel multiple If AND function

I have seen similar questions but I just can't seem to get this formula right.

I have three dropdowns on excel for country, setting and resource. The linked cells for these are I103 (Resource), I107 (Setting) and D145 (Country). The output is resource time, which is different for each country, setting and resource.

So far, I have three separate formulas for when the resource is changed to 1, 2 or 3

=IF(I103=1,IF(AND(I107=1,D145=1),E190,IF(AND(I107=1,D145=2),E200,IF(AND(I107=1,D145=3),E213,IF(AND(I107=2,D145=1),E151,IF(AND(I107=2,D145=2),E161,IF(AND(I107=2,D145=3),E174)))))))


=IF(I103=2,IF(AND(I107=1,D145=1),I190,IF(AND(I107=1,D145=2),I200,IF(AND(I107=1,D145=3),I213,IF(AND(I107=2,D145=1),I151,IF(AND(I107=2,D145=2),I161,IF(AND(I107=2,D145=3),I174)))))))


=IF(I103=3,IF(AND(I107=1,D145=1),M190,IF(AND(I107=1,D145=2),M200,IF(AND(I107=1,D145=3),M213,IF(AND(I107=2,D145=1),M151,IF(AND(I107=2,D145=2),M161,IF(AND(I107=2,D145=3),M174)))))))

However, what I need is for them to be combined all into one formula. I keep getting VALUE when I try to combine them with a comma.

Can anyone help?

Thank you very much

Aucun commentaire:

Enregistrer un commentaire