SOLVED
Home

If, then formula

%3CLINGO-SUB%20id%3D%22lingo-sub-535200%22%20slang%3D%22en-US%22%3EIf%2C%20then%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-535200%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20to%20figure%20out%20how%20much%20vacation%20time%20I'll%20have.%20From%201-36%20months%20of%20service%2C%20I%20get%2011%20hours%20per%20month.%20Then%2C%20from%2037-120%20months%20of%20service%2C%20I'll%20get%2014%20hours%20per%20month.%20From%20121-180%20months%20of%20service%2C%20I'll%20get%2016%20hours%20per%20month.%20How%20can%20I%20combine%20all%20of%20these%3F%20Say%20I%20have%2046%20months%20of%20service%2C%20I'll%20need%20to%20show%20that%20the%20first%2036%20months%20are%20at%20a%20rate%20of%2011%20hours%20per%20month%20and%20that%20every%20month%20after%20that%20(from%20month%2037%20to%2046)%20is%20at%20a%20rate%20of%2014%20hours%20per%20month.%20I%20would%20like%20to%20fill%20out%20the%20%22annual%20leave%20earned%22%20cell%20based%20on%20the%20%22service%20months%20to%20date%22%20and%20%22annual%20leave%20accrual%20rates%22%20table%20in%20my%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20836px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112285i94A8D0C74213582F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.JPG%22%20title%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-535200%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-535500%22%20slang%3D%22en-US%22%3ERe%3A%20If%2C%20then%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-535500%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F335107%22%20target%3D%22_blank%22%3E%40_janelllllle%3C%2FA%3E%26nbsp%3B%2C%20you%20may%20add%20helper%20range%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20618px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112297i78A5AB975CD6B837%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((B9-%24B%243%3A%24B%247)*%24C%243%3A%24C%247*((B9-%24B%243%3A%24B%247)%26gt%3B0))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-536383%22%20slang%3D%22en-US%22%3ERe%3A%20If%2C%20then%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-536383%22%20slang%3D%22en-US%22%3EThis%20works%20perfectly!%20Thank%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-536559%22%20slang%3D%22en-US%22%3ERe%3A%20If%2C%20then%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-536559%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F335107%22%20target%3D%22_blank%22%3E%40_janelllllle%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
_janelllllle
New Contributor

I need a formula to figure out how much vacation time I'll have. From 1-36 months of service, I get 11 hours per month. Then, from 37-120 months of service, I'll get 14 hours per month. From 121-180 months of service, I'll get 16 hours per month. How can I combine all of these? Say I have 46 months of service, I'll need to show that the first 36 months are at a rate of 11 hours per month and that every month after that (from month 37 to 46) is at a rate of 14 hours per month. I would like to fill out the "annual leave earned" cell based on the "service months to date" and "annual leave accrual rates" table in my worksheet.

 

Capture.JPG

3 Replies
Solution

@_janelllllle , you may add helper range like this

image.png

The formula could be

=SUMPRODUCT((B9-$B$3:$B$7)*$C$3:$C$7*((B9-$B$3:$B$7)>0))
This works perfectly! Thank you.

@_janelllllle , you are welcome