Excel Multiple Excel logical tests

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.
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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 :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top