Need help with between formula

[nesrawi]

Hi There, see red arrows on attached picture and then see below.
Please see red arrows.jpg
ROW 38 is the total of units and row u38 is total commissions, therefore if row r38 is less than 0.5 or over 9 then i need row u38 to show 0, and then if row r38 is between 0.5 and 8.5 then row 38 shows the total amount.

Thanks

[JeteMc]

Seems like the following formula would work, of course it would be easier to help if you could "Go Advanced" (button in beneath the "Quick Reply" box) and up load a sample worksheet (not just a picture) with expected results of the formula manually applied:

Please Login or Register  to view this content.

Let me know if you have any questions.

[nesrawi]

2016 Sales Updated.xlsx

Hi here is the attachment I will try the formula you sent earlier and let you know if it works. Just in case you need to work or check the attachment please see below

ROW 38 is the total of units and row u38 is total commissions, therefore if row r38 is less than 0.5 or over 9 then i need row u38 to show 0, and then if row r38 is between 0.5 and 8.5 then row 38 shows the total amount.

[nesrawi]

Hi JeteMc your formula worked, thank you. However if I need row U34 to show 0when row N37 is what formula shall I use? Please see attached for more details.

Thank you

2016 Sales Updated.xlsx

[JeteMc]

In response to post #3. This is still assuming a few things, due to all the values being included in U41:X41 despite the condition in post #1 that U41 should show 0 unless R38 was a value between 0.5 and 8.5. I am going to assume that the condition in post #1 is what is being looked for so that since R38 is 15 that the only one of those cells that should have a value other than zero is W38. If that is a correct assumption then I would suggest the formula formula pasted in U41 and dragged across to W41:

Please Login or Register  to view this content.

There would have to be a modification in X41 due to the entry in X31:

Please Login or Register  to view this content.

Let me know if you have any questions.

[JeteMc]

However if I need row U34 to show 0when row N37 is what formula shall I use? Please see attached for more details.

I am guessing that you want in U34 (CSI Bonus), to be based on what is in N38. If correct then I feel that the following formula will do what you want:

Please Login or Register  to view this content.

[JeteMc]

Thank You for the feedback and added reputation. If you haven't already, please take a moment to mark the thread 'Solved' using the thread tools link above your first post. Hope that you have a nice day.

[nesrawi]

Good morning, I am using the 1st formula in N38, O38, P38, and Q38. I will try the lase formula I received from you and will let know know the results.

Thank you.

[nesrawi]

2 questions for you what formula shall I use for the volume bonus and the CSI bonus, please see below?

1st Question
Volume Bonus opportunity “NOT RETRO ACTIVE”

a. 10 Units including 1 CPO = $1500.00

b. 16 Units Including 2 CPO = $2000.00

c. 20 Units including 3 CPO = $3000.00

d. 21 Units and over including 4 CPO $4000.00

2nd Question
CSI Bonus opportunity minimum 3 surveys Qualifier.

a. $100.00 Per New and CPO Units if my score is on or above Region Average

b. $200.00 Per New and CPO Units if my score is above $980.00

c. $250.00 Per New and CPO Units if my score is 1000

Thanks

[JeteMc]

As to the formula for Unit Bonus using the table in T3:V6 as well as the CPO count in X3 and the sum of units in R37 although I am not sure what "Not Retro Active" means. I assume that the two conditions given are minimum standards, if so this is a formula that takes the lesser of the two values:

Please Login or Register  to view this content.

As to the formula for CSI bonus I assume that the Region Average is 970 and that the formula would simply lookup the amount of the bonus at each minimum level multiplied by the sum of X2:X3:

Please Login or Register  to view this content.

Let me know if you have any questions.