+ Start a Discussion
LFaroLFaro 

EASY FORMULA HELP!! I'm driving myself crazy!

I wanted to know if someone could help me with a formula as I've been struggling with it. I am so horrible at formulas, but I know this one should be so easy!!

Here are the details:

REFERENCE FIELD = Total Hours
FORMULA = I'm attempting to create a custom field (called HOUR BUCKET) that is a TEXT FORMULA field that will take the "TOTAL HOURS" and break it out into buckets.

For example - in English this is how the formula would be:

1.) If "Total Hours" is less than or equal to 40 hours = the HOUR BUCKET should populate with "40 hours or less"
-or-
2.) if "Total Hours" is more than 40 AND less than 100 hours = the HOUR BUCKET should populate with "41 - 99 hours"
- or -
3.) if "Total Hours" is more than 99 AND less than 500 hours = the HOUR BUCKET should populate with "100 - 499 hours"
- or -
4.) If "Total Hours" is greater than 499 = the HOUR BUCKET should populate with "500+ hours"

I'm having trouble with all the "ANDS" and "ORs" and "IFs" and where to put the appropriate quotes and parenthesis.

Please please help me! Thank you so much!!!
NPMNPM

Try this:

Code:
IF (Total_Hours__c 
 <=40 ,"40 hours or less", 
IF (AND(Total_Hours__c 
  > 40,Total_Hours__c   < 100),"41 - 99 hours",
IF (AND(Total_Hours__c 
  > 99,Total_Hours__c   < 500),"100 - 499 hours",
IF (Total_Hours__c 
  > 499 ,"500+ hours",
"Undefined")
)))

The "Undefined" is a condition that should never be hit but you need to have something there for the syntax.
 

lmjohnsonlmjohnson
You can even simplify it more by using this code: It will look at the first statement and if it does not meet the criteria will move to the next statement and so on.

IF (Total_Hours__c
IF (Total_Hours__c
< 100,"41 - 99 hours",
IF (Total_Hours__c
< 500,"100 - 499 hours",
IF (Total_Hours__c
> 499 ,"500+ hours",
"Undefined")
)))