Here's the detail:
If the value in cell E2 ranges from 0- 1.14, I need cell F2 to read "NEITHER"
If the value in cell E2 ranges from 1.15-1.374, I need cell F2 to read "GOAL"
If the value in cell E2 ranges from 1.375 and up, I need cell F2 to read "STRETCH"
Is this possible?
Last edited by brooke711; 03-09-2011 at 11:21 AM.
Try this (but you may need an additional IF() to account for less than 0 or blank):
=IF(E2<1.15,"NEITHER",IF(E2<1.375,"GOAL","STRETCH"))
that worked perfectly! Thank you!!!!
Actually...I need to include more information now. We keep changing the qualifications for incentives
If the value in cell E2 ranges from 0- 1.14, I need cell F2 to read "NEITHER"
If the value in cell E2 ranges from 1.15-1.374 AND the value in cell F2 is 20% or less, I need cell F2 to read "GOAL"
If the value in cell E2 ranges from 1.375 and up AND the value in cell F2 is 15% or less, I need cell F2 to read "STRETCH"
Sorry it wasn't all included the first go round. I have tried to add the additional info but apparently I am missing something!
The value in cell F2 is the result of your formula so you can't have this formula checking itself for a value less than 20%. Circular reference!
oops! okay...the words NEITHER, GOAL and STRETCH should populate in G2. Sorry...forgot to update that part!Originally Posted by Cutter
Try this - it assumes you want a 'blank' if none of the 3 conditions are true:
=IF(E2<1.15,"NEITHER",IF(AND(E2<1.375,F2<=0.2),"GOAL",IF(AND(E2>=1.375,F2<=0.15),"STRETCH","")))
wow...you are the excel master! thank you SO much!
Not even close - but thanks!
And you're welcome. Don't forget to mark your thread as SOLVED (see FAQ at top of forum page for directions if needed).
okay, I am back. There is one hang up to this, and it's my fault because I left out something very important. Can you take one more stab and then I will be set? I have tried to adjust it myself, but once again, I am not understanding something.
If the value in cell E2 ranges from 0- 1.14, I need cell F2 to read "NEITHER" (done)
If the value in cell E2 ranges from 1.15-1.374 AND the value in cell F2 is 20% or less, I need cell F2 to read "GOAL" (done)
If the value in cell E2 ranges from 1.375 and up AND the value in cell F2 is 15% or less, I need cell F2 to read "STRETCH" (done)
If the value in cell E2 ranges from 1.375 and up AND the value in cell F2 is 20% or less, I need cell F2 to read "GOAL"
I think you're just changing the second condition - not adding an additional one. Correct?
Like this:
=IF(E2<1.15,"NEITHER",IF(AND(E2>=1.15,F2<=0.2),"GOAL",IF(AND(E2>=1.375,F2<=0.15),"STRETCH","")))
When I use that one there are 2 errors, so maybe it will help if I explain what I am trying to determine with these.
If an individual completes an average of at least 1.15/hr (this is calculated in cell E2) with a rate of 20% or less returned (this is calculated in cell F2), they have met goal.
If an individual completes an average of at least 1.375/hr (this is calculated in cell E2) with a rate of 20% or less returned (this is calculated in cell F2), they have met goal.
If an individual completes an average of at least 1.375/hr with a rate of 15% or less, they have met stretch.
If an individual falls below 1.15 OR above 20%, they don't qualify at all.
Cell G2 is where it will hopefully read GOAL, STRETCH or NEITHER based on this criteria to help determine eligibility and payout amounts for incentives without as much manual calculation, but now I think I am just putting the manual work on someone else. Do you get compensated for this?? I hope so!
It would be best if you could upload a small sample file (without personal data) that shows your setup and the errors you are getting. Include the results you want to get.
If needed, click the FAQ button at top of forum page and follow the links for instructions to upload a file.
But, based on your latest explanation, I think you might be able to use this:
=IF(AND(E2>=1.15,F2<=0.2),"GOAL",IF(AND(E2>=1.375,F2<=0.15),"STRETCH","NEITHER"))
Again, your first 2 scenarios in your latest post can be reduced to 1 (by using the lower E2 value) because they yield the same result. In other words, the 2nd scenario is redundant.
Last edited by Cutter; 03-04-2011 at 07:21 PM.
sample.xls
I've highlighted the rows that are incorrect and what the result should be. Hope this helps!
OK
The one in E4 failed because you were referring to the line above in the formula.
But the one in E6 failed because testing order was reversed (my fault) so the correct formula would be:
In E2 and copied down:
=IF(AND(C2>=1.375,D2<=0.15),"STRETCH",IF(AND(C2>=1.15,D2<=0.2),"GOAL","NEITHER"))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks