Excel Multiple Excel logical tests

A

Arsalan Musharraf

Joined
Sep 23, 2016
Messages
1
Reaction score
0
Hi,

I am trying to figure out how can I set a if logical formula.

The data is as follows:

Sales Target: 28000
Conversion Target: 66%
AOV Target: 110

Achievement: 29000
Conversion achievement:68
AOV: 112

I want to set a formula to calculate the team bonus. If they hit all 3 above targets they will get 20% of the sales increment. The sales was increased by £1000 so the incentive will be £200, If they don't hit any one of the above target they will get nothing.
 
Welcome to the forum and apologies for my late reply!

You will need to use an IF function together with an AND function, as follows...

I'll assume the following cell locations:

Sales target: A1
Conversion target: A2
AOV target: A3

Actual sales: B1
Actual conversion: B2
Actual AOV: B3​

and so the formula would be:

=(IF(AND(B1>=A1,B2>=A2,B3>=A3),(B1-A1)*20%,0))

Let me know if it works how you want :)
 
Back
Top