Forum Discussion

Jihad Al-Jarady's avatar
Jihad Al-Jarady
Steel Contributor
Dec 02, 2017

What function use to answer this question

Hi folks,

I am preparing for Excel exam MOS 2016, while I was practicing using a demo exercise, I saw this question, which I am not happy with the way the answered it.

 

Insert a formula into cell G4 that evaluates whether the amount is Parts, Accessories or Services exceed the Quarterly Goal.
For each column that exceeds the goal apply the Quarterly Bouns Rate

 

Please I need to know what is the best answer according to the MOS exam team.

Formulas & Functions
Tutorial
  • Mark Fitzgerald's avatar
    Mark Fitzgerald
    Iron Contributor
    Dec 03, 2017

    Use the SUMIF formula:

     

    In G4 enter 

    =SUMIF(B4:D4,">"&$B$18)*$B$17

    If you then drag this formula down the references to B4:D4 will increment to B5:D5 while references to $B$18 and $B$17 will remain fixed.

Resources