Mar 10 2019 12:11 PM
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!
Mar 10 2019 01:04 PM
SolutionThat 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"))))
Mar 10 2019 04:15 PM
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.
Mar 10 2019 06:27 PM
Thank you, this is very helpful information.
Mar 10 2019 01:04 PM
SolutionThat 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"))))