 # IF Function

Is there a way to make an if function only return true values? In the example below what needs to go in the False position in order to make Excel recalculate until a True value is given?

IF(A1<10, A1, ?)
5 Replies

# Re: IF Function

@Rossman1 Perhaps you can use this:

IF(A1<10, A1, "")

That will return the value of A1 if the condition is met. Otherwise, it will return nothing.

# Re: IF Function

Okay, how do I make only A1 be the return. Let’s say A1 is the sum of 5 numbers randomly generated between 1 and 5. But we only want a sum less than 10, and a value must be returned, so “nothing”, would trigger a recalculation until an answer is provided.

# Re: IF Function

@Rossman1 when you write "Let's say A1 is the sum of 5 numbers.......", I wonder if it really is so. Why not upload a file with the exact same structure as what you are dealing with. Then, it will be a lot easier to come up with a working solution, that probably requires some VBA programming. Having said that, VBA isn't really my favourite way to spend time. Someone else can step in to help you.

# Re: IF Function

Here is a copy of the file.

# Re: IF Function

@Rossman1 Sorry! Can't help you with triggering a recalculation if a range adds up to more than a certain amount. As said, that would probably require VBA.

But you might find one formula simplification helpful. Since your Excel version seems to support dynamic arrays, change this monster of a formula:

``=SUM(IF(E4=\$B\$4,\$A\$4,IF(E4=\$B\$5,\$A\$5,IF(E4=\$B\$6,\$A\$6,IF(E4=\$B\$7,\$A\$7,IF(E4=\$B\$8,\$A\$8,IF(E4=\$B\$9,\$A\$9,IF(E4=\$B\$10,\$A\$10,IF(E4=\$B\$11,\$A\$11,IF(E4=\$B\$12,\$A\$12,IF(E4=\$B\$13,\$A\$13)))))))))),IF(E5=\$B\$4,\$A\$4,IF(E5=\$B\$5,\$A\$5,IF(E5=\$B\$6,\$A\$6,IF(E5=\$B\$7,\$A\$7,IF(E5=\$B\$8,\$A\$8,IF(E5=\$B\$9,\$A\$9,IF(E5=\$B\$10,\$A\$10,IF(E5=\$B\$11,\$A\$11,IF(E5=\$B\$12,\$A\$12,IF(E5=\$B\$13,\$A\$13)))))))))),IF(E6=\$B\$4,\$A\$4,IF(E6=\$B\$5,\$A\$5,IF(E6=\$B\$6,\$A\$6,IF(E6=\$B\$7,\$A\$7,IF(E6=\$B\$8,\$A\$8,IF(E6=\$B\$9,\$A\$9,IF(E6=\$B\$10,\$A\$10,IF(E6=\$B\$11,\$A\$11,IF(E6=\$B\$12,\$A\$12,IF(E6=\$B\$13,\$A\$13)))))))))),IF(E7=\$B\$4,\$A\$4,IF(E7=\$B\$5,\$A\$5,IF(E7=\$B\$6,\$A\$6,IF(E7=\$B\$7,\$A\$7,IF(E7=\$B\$8,\$A\$8,IF(E7=\$B\$9,\$A\$9,IF(E7=\$B\$10,\$A\$10,IF(E7=\$B\$11,\$A\$11,IF(E7=\$B\$12,\$A\$12,IF(E7=\$B\$13,\$A\$13)))))))))),IF(E8=\$B\$4,\$A\$4,IF(E8=\$B\$5,\$A\$5,IF(E8=\$B\$6,\$A\$6,IF(E8=\$B\$7,\$A\$7,IF(E8=\$B\$8,\$A\$8,IF(E8=\$B\$9,\$A\$9,IF(E8=\$B\$10,\$A\$10,IF(E8=\$B\$11,\$A\$11,IF(E8=\$B\$12,\$A\$12,IF(E8=\$B\$13,\$A\$13)))))))))))``

to this:

``=SUM(XLOOKUP(E4#,options,costs))``

where "options" and "costs" are named ranges to the obvious lookup and return ranges.