Hello,

I'm trying to create a formula that will calculate the points if an indivial person has exceedes a certain amount of sales.

Please see my table below:

Sheet 1:

A6 Fred F6 $8275

A7 June F7 $7015

A8 Sally F8 $9885

A9 Kate F9 $8570Sheet2:

Sales Points

A5 7000 B5 1

A6 8000 B6 2

A7 9000 B7 3

A8 10000 B8 4

A9 11000 B9 5

A10 12000 B10 6Basically in sheet1, I need to show thaT for eg if Fred's total Sales is >7000<8000, then he will earn 1point. IF Fred's total Sales is >8000<9000, then he will earn 2 points and so on...

I tried : =IF(AND(F6<Points!A5,Sheet1!F6>Points!A6),Points!B5,IF(Sheet1!F6<Points!A6,Sheet1!F6>Points!A7,Points!B6))...and so on.

I got the message that I had too many arguments.

Can anyone please suggest an alternative formula??Thanks very much.

Jess

Ask Your Question

Weekly Poll