IF Function

[Rossman1]

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, ?)

[#2]

@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.

[#3]

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.

[#4]

@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.

[#5]

@Riny_van_Eekelen 

Here is a copy of the file. 

[#6]

@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.