vendredi 30 juin 2017

nested IF, AND, OR function compare and give result excel

Need help with this function.

=IF(AND($F5="Miks M",OR($G5="Miks M")),0,IF(AND($F5="Miks M",OR($G5="Miks L")),1,IF(AND($F5="Miks M",OR($G5="Miks XL")),2,IF(AND($F5="Miks M",OR($G5="Miks S")),-1,IF(AND($F5="Miks M",OR($G5="Miks XL PLUS")),3,IF(AND($F5="Miks M",OR($G5="Miks XXL")),4,IF(AND($F5="Miks M",OR($G5="Super")),5,IF(AND($F5="Miks M",OR($G5="Prica Plus")),-2,IF(AND($F5="Miks S",OR($G5="Prica Plus")),-1,IF(AND($F5="Miks S",OR($G5="Miks S")),0,IF(AND($F5="Miks S",OR($G5="Miks M")),1,IF(AND($F5="Miks S",OR($G5="Miks L")),2,IF(AND($F5="Miks S",OR($G5="Miks XL")),3,IF(AND($F5="Miks S",OR($G5="Miks XL PLUS")),4,IF(AND($F5="Miks S",OR($G5="Miks XXL")),5,IF(AND($F5="Prica Plus",OR($G5="Prica Plus")),0,IF(AND($F5="Prica Plus",OR($G5="Miks S")),1,IF(AND($F5="Prica Plus",OR($G5="Miks M")),2,IF(AND($F5="Prica Plus",OR($G5="Miks L")),3,IF(AND($F5="Prica Plus",OR($G5="Miks XL")),4,IF(AND($F5="Prica Plus",OR($G5="Miks XL PLUS")),5,IF(AND($F5="Prica Plus",OR($G5="Miks XXL")),6,IF(AND($F5="Miks L",OR($G5="Prica plus")),-3,IF(AND($F5="Miks L",OR($G5="Miks S")),-2,IF(AND($F5="Miks L",OR($G5="Miks M")),-1,IF(AND($F5="Miks L",OR($G5="Miks L")),0,IF(AND($F5="Miks L",OR($G5="Miks XL")),1,IF(AND($F5="Miks L",OR($G5="Miks XL PLUS")),2,IF(AND($F5="Miks L",OR($G5="Miks XXL")),3,IF(AND($F5="Miks L",OR($G5="Super")),4,IF(AND($F5="Miks XL",OR($G5="Prica plus")),-4,IF(AND($F5="Miks XL",OR($G5="Miks S")),-3,IF(AND($F5="Miks XL",OR($G5="Miks M")),-2,IF(AND($F5="Miks XL",OR($G5="Miks L")),-1,IF(AND($F5="Miks XL",OR($G5="Miks XL")),0,IF(AND($F5="Miks XL",OR($G5="Miks XL PLUS")),1,IF(AND($F5="Miks XL",OR($G5="Miks XXL")),2,IF(AND($F5="Miks xL",OR($G5="Super")),3,IF(AND($F5="Miks XL Plus",OR($G5="Prica plus")),-5,IF(AND($F5="Miks XL Plus",OR($G5="Miks S")),-4,IF(AND($F5="Miks XL Plus",OR($G5="Miks M")),-3,IF(AND($F5="Miks XL Plus",OR($G5="Miks L")),-2,IF(AND($F5="Miks XL Plus",OR($G5="Miks XL")),-1,IF(AND($F5="Miks XL Plus",OR($G5="Miks XL PLUS")),0,IF(AND($F5="Miks XL Plus",OR($G5="Miks XXL")),1,IF(AND($F5="Miks xL Plus",OR($G5="Super")),2,IF(AND($F5="Miks XXL",OR($G5="Prica plus")),-6,IF(AND($F5="Miks XXL",OR($G5="Miks S")),-5,IF(AND($F5="Miks XXL",OR($G5="Miks M")),-4,IF(AND($F5="Miks XXL",OR($G5="Miks L")),-3,IF(AND($F5="Miks XXL",OR($G5="Miks XL")),-2,IF(AND($F5="Miks XXL",OR($G5="Miks XL PLUS")),-1,IF(AND($F5="Miks XXL",OR($G5="Miks XXL")),0,IF(AND($F5="Miks xXL",OR($G5="Super")),1,IF(AND($F5="Super",OR($G5="Prica plus")),-7,IF(AND($F5="Super",OR($G5="Miks S")),-6,IF(AND($F5="Super",OR($G5="Miks M")),-5,IF(AND($F5="Super",OR($G5="Miks L")),-4,IF(AND($F5="Super",OR($G5="Miks XL")),-3,IF(AND($F5="Super",OR($G5="Miks XL PLUS")),-2,IF(AND($F5="Super",OR($G5="Miks XXL")),-1,IF(AND($F5="Super",OR($G5="Super")),0))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Is there any chance to this function become shorter and simpler? For now its working perfect but if it can be shorter it would be much better. If cell F5 is Miks M and cell G5 is Miks M write 0 in H5 if G5 is Miks L write 1 in H5 and soo on.

Aucun commentaire:

Enregistrer un commentaire