IF Function

%3CLINGO-SUB%20id%3D%22lingo-sub-2805014%22%20slang%3D%22en-US%22%3EIF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805014%22%20slang%3D%22en-US%22%3EIs%20there%20a%20way%20to%20make%20an%20if%20function%20only%20return%20true%20values%3F%20In%20the%20example%20below%20what%20needs%20to%20go%20in%20the%20False%20position%20in%20order%20to%20make%20Excel%20recalculate%20until%20a%20True%20value%20is%20given%3F%3CBR%20%2F%3E%3CBR%20%2F%3EIF(A1%26lt%3B10%2C%20A1%2C%20%3F)%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2805014%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2805038%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805038%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1172828%22%20target%3D%22_blank%22%3E%40Rossman1%3C%2FA%3E%26nbsp%3BPerhaps%20you%20can%20use%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIF(A1%26lt%3B10%2C%20A1%2C%20%22%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThat%20will%20return%20the%20value%20of%20A1%20if%20the%20condition%20is%20met.%20Otherwise%2C%20it%20will%20return%20nothing.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2805061%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805061%22%20slang%3D%22en-US%22%3EOkay%2C%20how%20do%20I%20make%20only%20A1%20be%20the%20return.%20Let%E2%80%99s%20say%20A1%20is%20the%20sum%20of%205%20numbers%20randomly%20generated%20between%201%20and%205.%20But%20we%20only%20want%20a%20sum%20less%20than%2010%2C%20and%20a%20value%20must%20be%20returned%2C%20so%20%E2%80%9Cnothing%E2%80%9D%2C%20would%20trigger%20a%20recalculation%20until%20an%20answer%20is%20provided.%3C%2FLINGO-BODY%3E
New Contributor
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

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

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.

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

@Riny_van_Eekelen 

Here is a copy of the file. 

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