SOLVED

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

Copper 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
best response confirmed by fish_hog (Copper Contributor)
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"))))

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.

Thank you, this is very helpful information. 

1 best response

Accepted Solutions
best response confirmed by fish_hog (Copper Contributor)
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"))))

View solution in original post