Spreadsheet: 864543d1711671907-if-cell-blank-return-the-value-to-on-or-another-cell-forms
Sheet: DONNEES & CALCULS MPs

 | RM | QTE DANS LA COMPO | % DANS LA MP | RESULTAT |  | RM | QTE DANS LA COMPO | % DANS LA MP | RESULTAT |  | RM | QTE DANS LA COMPO | % DANS LA MP | RESULTAT |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | the value of BL20 and BU20 dependent on the vaalue in BL18 and BU 18 IF BL20 and BU20 are < 0 the result in D20 is correct cause the formula calculate the difference between a positive and negative value  for example if [NAME] and BU18=2 then we have the sum of -9,400 and -6,249= -15,649 from which we do the substraction of 4,351 (3,751+0,600) = -11,30 in D20 which is ok  if ever the value in BL20 and/or BU20 and/or the other cells till the end is positve the formula doesn't consider the positive value but only the negative. So if  [NAME] and BU18=40 we have 3,751+12 = 15,751 and -6,249. So I need to make him understand that he has to consider the positive value as a additional quantity and then we'll have -4,249 in G20 (-6,249+2). so the result in D20 will be correct
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | touslesE
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2 |  |  | 3 |  |  | 4 |  |  | 2 |  |  | 3 |  |  | 4 |  |  | 2 |  |  | 3 |  |  | 4 |  |  | 2 |  |  | 3 |  |  | 4 |  |  | 2 |  |  | 3 |  |  | 4 |  |  | 2 |  |  | 3 |  |  | 4 |  |  | 2 |  |  | 3 |  |  | 4 |  |  | 2 |  |  | 3 |  |  | 4 |  |  | 2 |  |  | 3 |  |  | 4 |  |  | 2 |  |  | 3 |  |  | 4 |  |  | 2 |  |  | 3 |  |  | 4 |  |  | 2 |  |  | 3 |  |  | 4 |  |  | 2 |  |  | 3 |  |  | 4
 |  |  | A METTRE DANS L'ESSAI |  |  |  |  | A METTRE DANS L'ESSAI |  |  |  |  | A METTRE DANS L'ESSAI |  |  | Essai 1 |  |  | Essai 2 |  |  | Essai 3 |  |  | Essai 1 |  |  | Essai 2 |  |  | Essai 3 |  |  | Essai 1 |  |  | Essai 2 |  |  | Essai 3 |  |  | Essai 1 |  |  | Essai 2 |  |  | Essai 3 |  |  | Essai 1 |  |  | Essai 2 |  |  | Essai 3 |  |  | Essai 1 |  |  | Essai 2 |  |  | Essai 3 |  |  | Essai 1 |  |  | Essai 2 |  |  | Essai 3 |  |  | Essai 1 |  |  | Essai 2 |  |  | Essai 3 |  |  | Essai 1 |  |  | Essai 2 |  |  | Essai 3 |  |  | Essai 1 |  |  | Essai 2 |  |  | Essai 3 |  |  | Essai 1 |  |  | Essai 2 |  |  | Essai 3 |  |  | Essai 1 |  |  | Essai 2 |  |  | Essai 3 |  |  | Essai 1 |  |  | Essai 2 |  |  | Essai 3
 |  |  | 3 | 4 | 5 |  |  |  |  |  |  | IFNA(@INDEX(natform.type;MATCH(@RM.colonneR;natform.colonneS;0));"") |  |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE |  |  | CODE
 | [NAME] |  |  |  |  | Essai 1 |  | Essai 2 |  | Essai 3 |  |  |  |  |  | [NAME] |  |  | [NAME] |  |  | [NAME] |  |  | BERGAMOTE INCOLORE HE |  |  | BERGAMOTE INCOLORE HE |  |  | BERGAMOTE INCOLORE HE |  |  | BERGAMOTE TERPENES |  |  | BERGAMOTE TERPENES |  |  | BERGAMOTE TERPENES |  |  | BERGAMOTE SUBSTITUT |  |  | BERGAMOTE SUBSTITUT |  |  | BERGAMOTE SUBSTITUT |  |  | BERGAMOTE SUBSTITUT SANS CMR |  |  | BERGAMOTE SUBSTITUT SANS CMR |  |  | BERGAMOTE SUBSTITUT SANS CMR |  |  | MANDARINE JAUNE HE |  | 22 | MANDARINE JAUNE HE |  |  | MANDARINE JAUNE HE |  |  | MANDARINE VERTE HE |  | 40 | MANDARINE VERTE HE |  |  | MANDARINE VERTE HE |  |  | MANDARINE INCOLORE DMC HE |  |  | MANDARINE INCOLORE DMC HE |  |  | MANDARINE INCOLORE DMC HE |  |  | MANDARINE TERPENES |  |  | MANDARINE TERPENES |  |  | MANDARINE TERPENES |  |  | MANDARINE JAUNE HE SUBSTITUT |  |  | MANDARINE JAUNE HE SUBSTITUT |  |  | MANDARINE JAUNE HE SUBSTITUT |  |  | CITRON ESPAGNE HE |  |  | CITRON ESPAGNE HE |  |  | CITRON ESPAGNE HE |  |  | CITRON LAVE HE |  |  | CITRON LAVE HE |  |  | CITRON LAVE HE
 | RAW MATERIALS | TOTAL APPORT | Colonne52 | Colonne63 | Colonne14 | en moins | APPORTé | en moins. | APPORTé. | en moins.2 | APPORTé.2 | QTE TYPE | QTE ESSAI 1 | QTE ESSAI 2 | CAS | MP % | APPORTE7 | + / - | MP %2 | APPORTE2 | + / -2 | MP %3 | APPORTE3 | + / -3 | MP %4 | APPORTE4 | + / -4 | MP %5 | APPORTE5 | + / -5 | MP %6 | APPORTE6 | + / -6 | MP %7 | APPORTE8 | + / -9 | MP %210 | APPORTE211 | + / -212 | MP %313 | APPORTE314 | + / -315 | MP %416 | APPORTE417 | + / -418 | MP %519 | APPORTE520 | + / -521 | MP %622 | APPORTE623 | + / -624 | MP %72 | APPORTE83 | + / -94 | MP %2105 | APPORTE2116 | + / -2127 | MP %3138 | APPORTE3149 | + / -31510 | MP %41611 | APPORTE41712 | + / -41813 | MP %51914 | APPORTE52015 | + / -52116 | MP %62217 | APPORTE62318 | + / -62419 | MP %73 | APPORTE84 | + / -95 | MP %2106 | APPORTE2117 | + / -2128 | MP %3139 | APPORTE31410 | + / -31511 | MP %41612 | APPORTE41713 | + / -41814 | MP %51915 | APPORTE52016 | + / -52117 | MP %62218 | APPORTE62319 | + / -62420 | MP %74 | APPORTE85 | + / -96 | MP %2107 | APPORTE2118 | + / -2129 | MP %31310 | APPORTE31411 | + / -31512 | MP %41613 | [NAME] | + / -41815 | MP %51916 | APPORTE52017 | + / -52118 | MP %62219 | APPORTE62320 | + / -62421 | MP %75 | APPORTE86 | + / -97 | MP %2108 | [NAME] | + / -21210 | MP %31311 | APPORTE31412 | + / -31513 | MP %41614 | APPORTE41715 | + / -41816 | MP %51917 | APPORTE52018 | + / -52119 | MP %62220 | APPORTE62321 | + / -62422 | MP %416241111318511656565 | APPORTE417251212328612666666 | + / -418261313338713676767 | MP %519271414348814686868 | APPORTE520281515358915696969 | + / -521291616369016707070 | MP %622301717379117717171 | APPORTE623311818389218727272 | + / -624321919399319737373 | + / -31894 | + / -31898 | + / -31899
 | GAMMA TERPINENE |  |  |  |  |  |  |  |  |  |  | 10 |  |  |  | 7.14 |  |  | 7.14 |  |  | 7.14 |  |  | 2.48 |  |  | 2.48 |  |  | 2.48 |  |  | 3.53 |  |  | 3.53 |  |  | 3.53 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 17.05 |  |  | 17.05 |  |  | 17.05 |  |  | 30 |  |  | 16.79 |  |  | 16.79
 | METHYL ANTHRANILATE METHYLE |  |  |  |  |  |  |  |  |  |  | 0.1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.14 |  |  | 0.14 |  |  | 0.14 |  |  | 0.45 |  |  | 0.45 |  |  | 0.45
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.02 |  |  | 0.02 |  |  | 0.02
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.03 |  |  | 0.03 |  |  | 0.03
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.04 |  |  | 0.04 |  |  | 0.04
 | EXPECTED RESULT |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 9.8 |  |  | 9.8 |  |  | 9.8 |  |  | 10.01 |  |  | 10.01 |  |  | 10.01 |  |  | 2.76 |  |  | 2.76 |  |  | 2.76 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.11 |  |  | 0.11 |  |  | 0.11 |  |  | 0.16 |  |  | 0.16 |  |  | 0.16
 | GAMMA TERPINENE |  |  |  |  | -4.249 | 15.751 |  |  |  |  | 10 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 17.05 |  |  | 0.12 |  |  | 0.12 |  |  | 30 |  |  | 0.28 |  |  | 0.28
 | METHYL ANTHRANILATE METHYLE |  |  |  |  | 0.011 | 0.211 |  |  |  |  | 0.1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.14 |  |  |  |  |  |  |  |  | 0.45 |  |  | 0.28 |  |  | 0.28
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.91 |  |  | 0.91 |  |  | 0.91 |  |  | 2.94 |  |  | 2.94 |  |  | 2.94 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.24 |  |  | 0.24 |  |  | 0.24 |  |  | 0.3 |  |  | 0.3 |  |  | 0.3
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.67 |  |  | 0.67 |  |  | 0.67
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.07 |  |  | 0.07 |  |  | 0.07 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.69 |  |  | 0.69 |  |  | 0.69
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3.58 |  |  | 3.58 |  |  | 3.58 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.9 |  |  | 0.9 |  |  | 0.9 |  |  | 0.95 |  |  | 0.95 |  |  | 0.95
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 7.21 |  |  | 7.21 |  |  | 7.21 |  |  | 6.3 |  |  | 6.3 |  |  | 6.3 |  |  | 18.27 |  |  | 18.27 |  |  | 18.27 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1.39 |  |  | 1.39 |  |  | 1.39 |  |  | 1.64 |  |  | 1.64 |  |  | 1.64
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.95 |  |  | 0.95 |  |  | 0.95 |  |  | 1.51 |  |  | 1.51 |  |  | 1.51 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1.57 |  |  | 1.57 |  |  | 1.57 |  |  | 1.79 |  |  | 1.79 |  |  | 1.79
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1.04 |  |  | 1.04 |  |  | 1.04 |  |  | 6.28 |  |  | 6.28 |  |  | 6.28 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2.3 |  |  | 2.3 |  |  | 2.3 |  |  | 2.14 |  |  | 2.14 |  |  | 2.14
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 40.62 |  |  | 40.62 |  |  | 40.62 |  |  | 35.86 |  |  | 35.86 |  |  | 35.86 |  |  | 60.24 |  |  | 60.24 |  |  | 60.24 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 73.34 |  |  | 73.34 |  |  | 73.34 |  |  | 72.23 |  |  | 72.23 |  |  | 72.23
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.05 |  |  | 0.05 |  |  | 0.05