IF SUM

[TYE]

I NEED HELP ON THE FOLLOWING
SALES PRODUCTIVITY Attendance Total
PAUL SMITH 5 90% 100% £50

PRODUCTIVITY
89% OR LESS = £5
90% OR ABOVE = £10

Attendance
99% OR LESS = £5
100% OR ABOVE= £5

BUT IF THE HIT ONE AND NOT THE OTHER THEY STILL GET £5.00

EX--


SALES PRODUCTIVITY Attendance Total
PAUL SMITH 5 89% 100% £25

SALES PRODUCTIVITY Attendance Total
PAUL SMITH 5 90% 98% £25


So i need a formula that would work this out,

so if the hit both target they will get £10 for each sale

if they hit one and not the other they will get £5 not £10

if they not hit any they get £5

can you help me on this

 

[bernard liengme]

Not sure I understand the line :BUT IF THE HIT ONE AND NOT THE OTHER THEY
STILL GET £5.00

Try this asnd see if it gets you where you want to go:
=(5+(C5>=90%)*5) +(5+(D5>=100%)*5)
C5 is the perfromance % and D5 the attendence (how can this exceed 100?)

And please turn off the SHOUTING - caps in email are read as shouting!

best wishes
Bernard

 

[TYE]

this does not work this is what is sayying,

Sales Productivity Attendance Total
Paul Smith 1 75% 75% £10

The total should be £5 not £10

This is working it out, that if they get 1 sales but they don't hit target
they will get £10 in total it should be £5 not £10

So if they hit both targets (100% Attendance and 90% or above Productivity)
then they get £10 in total for every sale.

If they hit one target and not the other they will only get £5 a sale each

if they don't hit both hits they get £5 each sale



£5 = Productivity

 

[bernard liengme]

Now you have told us what you want more clearly
B5 - sales
C5 - performance
D5 - attendence
Bonus is =((C5>89%)*5 + (D5>99%)*5)*B5
Can you see how it works?

best wishes
Bernard