Need some help generating a formula

[NathanB]

Here's the deal: I need a formula that looks at cell (x) which is a percent. If the % is a positive number less than 5%, the result is $160.00. If the % is greater than 5%, the result is $320.00. If the % is a negative number less than -5%, the result is -$160.00. If the % is greater than -5%, the result is -$320.00.

For example, if the cell reads -6.20%, the result should be -$320.00. If the cell reads 1.24%, the result should be $160.00.


Thanks in advance for the assist!

 

[Peter_SSs]

What happens on the boundaries? eg exactly -5%, 0%, 5%

 

[NathanB]

At 0%, the result should be $0.00. 5% - positive or negative - is the change-over point. So 5% or > is $320.00, -5% or greater is -$320.00. Sorry I wasn't clear on that.

 

[Peter_SSs]

See if this does what you want.

BTW, I think you might have your 'greater' and 'less' mixed up for negative numbers. -6% is LESS than -5%.

Excel Workbook
	A	B
1	-6.20%	-320
2	1.24%	160
3	0.00%	0
4	-2.30%	-160
5	25.00%	320
% to number

 

[Peter_SSs]

.. or possibly this

=IF(A1=0,0,IF(ABS(A1)<0.05,160,320)*SIGN(A1))