IF statment with mutiple "value if true" (iterative)

[andrews25]

I have a cell where I am trying to determine a multiplier from 0 to 1 in column A to then be used to scale a value in column B that produces another value in column C. However if column C is greater than zero the multiplier must be reduced until column C is zero (i.e if column C is zero I want the multiplier to be 1, if it is greater than zero I want the multiplier to be as large as possible to produce a zero in column C). I have a poor attempt at this with the following if statement in column A:

=IF(C1>0,0.95,IF(C1>0,0.9,IF(C1>0,0.85,IF(C1>0,0.8,IF(C1>0,0.75,IF(C1>0,0.7,IF(C1>0,0.65,IF(C1>0,0.6,IF(C1>0,0.55,IF(C1>0,0.5,IF(C1>0,0.45,IF(C1>0,0.4,IF(C1>0,0.35,IF(C1>0,0.3,IF(C1>0,0.25,IF(C1>0,0.2,IF(C1>0,0.15,IF(C1>0,0.1,IF(C1>0,0.05,IF(C1>0,0,1))))))))))))))))))))

However this stops after the first IF and leaves me with 0.95 and does not continue to reduce the multiplier if the value is still greater than zero. I guess the objective I am trying to accomplish is IF C1>0 then 0.95, IF C1 is still>0, 0.9 and so on. If there is a better way to accomplish this I'd love the help.

 

[offthelip]

What you are trying to do is impossible with an If statement. It is NOT an interative statement, the formula takes the value of C1 just once and then tries to work out the whole statement. Which it suceeds at doing because C1 is greater than zero so the result is 0.95, excel doesn't bother to calculate the rest of the equation at all. If you do write the formula in such away that it appears to be iterative, you will just get a"circular Reference" error showing up, so it still won't do it.
there are two ways you can do what you want
1: Use the solver addin
2: Write a vBA routine to iterate to the solution you want.

 

[jtakw]

Hi,

I'm not understanding what you're trying to do, any non-zero positive number, no matter how many times you multiply it by any other non-zero positive number, will never make the first number result in zero, like as shown in C6 and D6 below, so maybe formula in D2 is what you're looking for?

Excel 2010
	C	D
1		Multiplier
2	0	1
3	-0.05	1
4	0.001	0
5
6	0.1	0.001
Sheet12

Cell Formulas
Range	Formula
D2	=IF(C2<=0,1,0)
D6	=C6*0.01

 

[Peter_SSs]

Welcome to the MrExcel board!

Any chance we could see a small set of sample data and the expected results with any further clarification that you could give?
My signature block below has a link for good ways to provide sample data that we can test with.