 • 540K Members
• 3,400 Online
• 644K Conversations
SOLVED

Highlighted

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

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

# Re: Need help writing a formula to perform different calculations if numbers fall within specific ra

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

# Re: Need help writing a formula to perform different calculations if numbers fall within specific ra

Thank you @Sergei Baklan , this seems to be working! :)

Highlighted

# Re: Need help writing a formula to perform different calculations if numbers fall within specific ra

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

# Re: Need help writing a formula to perform different calculations if numbers fall within specific ra

Thank you, this is very helpful information.