SOLVED
Home

Need help writing a formula to perform different calculations if numbers fall within specific ranges

%3CLINGO-SUB%20id%3D%22lingo-sub-363949%22%20slang%3D%22en-US%22%3ENeed%20help%20writing%20a%20formula%20to%20perform%20different%20calculations%20if%20numbers%20fall%20within%20specific%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363949%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20a%20formula%20to%20populate%20the%20G%20column%20based%20on%20data%20entered%20into%20the%20F%20column.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20if%20F1%3D0%2C%20then%20G1%20also%20%3D0.%3C%2FP%3E%3CP%3EIf%20F1%20is%20greater%20than%20zero%20and%20less%20than%201%2C667%2C%20then%20G1%3D500.%3C%2FP%3E%3CP%3EIf%20F1%20is%20greater%20than%20or%20equal%20to%201%2C667%2C%20and%20less%20than%206%2C667%2C%20then%20G1%3D500%2B(F1*0.3)%3C%2FP%3E%3CP%3EIf%20F1%20is%20greater%20than%20or%20equal%20to%206%2C667%2C%20then%20G1%3D2%2C000%2B(F1*0.1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20only%20seem%20to%20create%20a%20formula%20that%20returns%20FALSE%20in%20the%20G%20column.%20Can%20anybody%20help%20me%20create%20a%20working%20formula%20that%20will%20perform%20these%20calculations%3F%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-363949%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%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-364001%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20writing%20a%20formula%20to%20perform%20different%20calculations%20if%20numbers%20fall%20within%20specific%20ra%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364001%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%2C%20this%20is%20very%20helpful%20information.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363984%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20writing%20a%20formula%20to%20perform%20different%20calculations%20if%20numbers%20fall%20within%20specific%20ra%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363984%22%20slang%3D%22en-US%22%3E%3CP%3EThere%20are%20always%20alternatives%20in%20Excel%3A%3C%2FP%3E%3CP%3E%3D%20CHOOSE(%20MATCH(%20value%2C%20%7B0%2C1%2C1667%2C6667%7D%20)%2C%200%2C%20500%2C%20500%2B0.3*value%2C%202000%2B0.1*value%20)%3C%2FP%3E%3CP%3Ewhere%20'value'%20can%20refer%20to%20F1%20or%20a%20range%20that%20includes%20F1.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363980%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20writing%20a%20formula%20to%20perform%20different%20calculations%20if%20numbers%20fall%20within%20specific%20ra%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363980%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%20this%20seems%20to%20be%20working!%20%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363959%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20writing%20a%20formula%20to%20perform%20different%20calculations%20if%20numbers%20fall%20within%20specific%20ra%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363959%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIF(F1%26gt%3B%3D6667%2C2000%2B0.1*F1%2CIF(F1%26gt%3B%3D1667%2C500%2B0.3*F1%2CIF(F1%26gt%3B0%2C500%2CIF(F1%3D0%2C0%2C%22negative%20number%22))))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
fish_hog
New Contributor

I need to a formula to populate the G column based on data entered into the F column. 

So if F1=0, then G1 also =0.

If F1 is greater than zero and less than 1,667, then G1=500.

If F1 is greater than or equal to 1,667, and less than 6,667, then G1=500+(F1*0.3)

If F1 is greater than or equal to 6,667, then G1=2,000+(F1*0.1)

 

I can only seem to create a formula that returns FALSE in the G column. Can anybody help me create a working formula that will perform these calculations? Thank you!

4 Replies
Highlighted
Solution

That could be

=IF(F1>=6667,2000+0.1*F1,IF(F1>=1667,500+0.3*F1,IF(F1>0,500,IF(F1=0,0,"negative number"))))
Highlighted
Highlighted

There are always alternatives in Excel:

= CHOOSE( MATCH( value, {0,1,1667,6667} ), 0, 500, 500+0.3*value, 2000+0.1*value )

where 'value' can refer to F1 or a range that includes F1.

Highlighted

Thank you, this is very helpful information.