Forum Discussion

Danijel Radic's avatar
Danijel Radic
Copper Contributor
Mar 28, 2018

Multiplying with few conditions

Hi.

I need a help with some calculations where few conditions have to be met.

First is that values in column B have to meet values in column D. Then if so, the value in column B multiplies with the percentage in columns E-H depending on the PLAN (E11) and targets set in F10,G10, or H10, or with base percentage in column E if no PLAN 1,2, or 3 are met.

The result shuld be a provision written in column J.

I tried with IF formulas but i'm new with this and i cannot figure out the error.

Excel Desktop
Formulas & Functions

2 Replies

  • stephen zabiela's avatar
    stephen zabiela
    Copper Contributor
    Mar 28, 2018

    Detlef's solution using sumproduct is an elegant solution and one I would usually use ... but it doesn't tell you why you were not having success with the IF statement approach you were taking.  Try this ...

    =IF(AND(B3>=D3,E11>=H10),B3*H3,
    IF(AND(B3>=D3,E11>=G10),B3*G3,
    IF(AND(B3>=D3,E11>=F10),B3*F3,B3*E3
    )))

    You were pretty much there but didn't get the AND syntax quite right.

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Mar 28, 2018

    Danijel,

     

    my guess:

     =MAX(SUMPRODUCT(($E$11>=F$10:H$10)*(B3*F3:H3)*(B3>=D3)),B3*E3)