Question

what is the formula for E13

Marketing Analytics Bonus Data Rating Bonus 0% 5% Survey Score Rating Bonus Monthly Take Home w Onthly alary 4,167 6,271 5,604 3,832 4,896 5,083 in uw Jonathan Weiss E13 Whichever bonus rate in the table above applies to a rating score that is closest to the Survey Score without being above is applied to Monthly Salary to calculate the Rating Bonus (%), which is then multiplied by the Monthly Salary. This formula should be copied across E10-E15. 2/13/2020 12:56 PM Reply ill not te o ri e pro

Answer 1

In case of any query do comment. Please rate answer as well. Thanks

Answer: Use below formula:

=HLOOKUP(D13,$D$7:$G$8,2,TRUE)*C13

Explanation: We will use HLOOKUP here to fetch the closest bonus percentage based on the survey score.

It has four parameters. First parameter is the value to be searched in lookup array which is in cell D13 here,

second parameter is lookup array which is D7:G8, but we are using here as ,$D$7:$G$8 to get absolute reference so that when you copy the formula it should not change.

Third parameter is when any match is found which row to be picked , in our case we have to pick data from the second row,

Fourth parameter is TRUE for approximate match as we don't require exact match as per the data given

Then multiply it with the Month Salary Column which is in C13. It will give you the desired output.

Output:

E13 fr =HLOOKUP(D13, $D$7:$G$8,2,TRUE) *C13 A B C D к 1 Rating Bonus 3 2% 4 5% 0% 14 12 Survey Code Annual Salary Month Salary Survey Score Rating Bonus Monthly Take Home 13. 38078 50000 4167 208.35! 41105 75250 6271 3.5 125.42 39752 67250 5604 280.2 37872 45980 3832 76.64 40616 58750 4896 4.2 244.8 40347 61000 5083 4.5 254.15 4.2