While in cell G3,

If value in E3 is less than 28, enter value in G3,

If Value is greater than 28, enter 28(ps, I use this in both OpenOffice & Google Sheets)

✔ Best Answer

That is exactlywhat my suggestion does.=IF(E3<28,E3,28)

All you did was reverse the

logical_testand then swap thevalue_if_trueandvalue_if_false.You will note that with my suggestion, when E3 = 28, the

logical_testwill return FALSE since 28 is not less than 28. When thelogical_testreturns FALSE, thevalue_if_falseis return by the IF function and the result will be 28.While your solution definitely works, I'm only explaining how my suggestion works so that you get a deeper understanding of how the arguments for the IF function work.

Comparing my suggestion to yours and seeing why they both work should help you follow the logic of an IF function. Understanding how the

value_if_trueandvalue_if_falsearguments work is required once you move on to a Nested IF, where one or both of those arguments might be another IF function.For example, it appears that you want the maximum value of the function to be 28. Now let's add another requirement: the minimum value should be 10. Anything between 10 and 28 should return the value in E3. That would look like this:

=IF(E3>28,28,IF(E3<10,10,E3))

You will note that for the

logical_testE3>28, thevalue_if_falseis another IF which "tests" to see if E3 is less than 10. If so, it returns 10, otherwise it returns the value in E3.The main thing to keep in mind is that a Nested IF performs it's calculation from left to right and stops at the first

logical_testthat returns TRUE. In other words, it doesn't determine the result of everylogical_testand then decide which result to use. It merely moves from left to right and as soon as it finds something that is TRUE, it returns thevalue_if_truefor the preceedinglogical_test. If none of thelogical_testsare TRUE, then the entire IF returns the finalvalue_if_false. For that reason, you must make sure that you put yourlogical_testsin the correct order so that it doesn't stop before it should.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

if the value in E3 is 28 or less, show that result in G3, if E3 is 29 or greater that result should be 28 in G3

I tried putting a limit to value in column G but it won't show the value of E3 if less than 29 it doesn't show the value of E3Any help is very appreciated

message edited by Basecalc

The formula looks like this =IF(STATEMENT, VALUE_IF_TRUE, VALUE_IF_FALSE). You only have =IF(STATEMENT, VALUE_IF_TRUE). You may try this =IF(E3<28,G3,28), however in your example you don't tell us what should happen if E3 = 28, so this only gives 28 if E3 is not smaller than 28.

NigelWind slow

Technically, it's... =IF(

)logical_test, value_if_true, value_if_falseThe

logical_testreturns TRUE or FALSE, thus the IF returns either thevalue_if_trueorvalue_if_false.

message edited by DerbyDad03

I guess I explain as well as I write formulas, but if in cell E3 that value (say:19,which can change) is less than or equal to 28 it should return that value in E3 (19) but if it's greater than 28 (say:34) the result would be 28. I tried to explain it in the message edit just below my original question.

Thank you,

BC

message edited by Basecalc

=IF(logical_test, value_if_true, value_if_false) =IF(E3<28,E3,28)

logical_test: E3<28

value_if_true: E3

value_if_false: 28

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you Nigel Spike & DerbyDad03, you led me in a more simple solution to find out that rather than look at it from being less than, after about 8 hours of calc's, I went the other way and stumbled across this solution:

I moved column G to F and then used this statement in column G

=if(E3>28,28,E3) using Google Sheets, Excel 2010,& OO Calc →4.1.0

Doing this allows everything 28 and below to return their value and everything greater than 28 returns 28!Thank you Computing.Net

BC

message edited by Basecalc

That is exactlywhat my suggestion does.=IF(E3<28,E3,28)

All you did was reverse the

logical_testand then swap thevalue_if_trueandvalue_if_false.You will note that with my suggestion, when E3 = 28, the

logical_testwill return FALSE since 28 is not less than 28. When thelogical_testreturns FALSE, thevalue_if_falseis return by the IF function and the result will be 28.While your solution definitely works, I'm only explaining how my suggestion works so that you get a deeper understanding of how the arguments for the IF function work.

Comparing my suggestion to yours and seeing why they both work should help you follow the logic of an IF function. Understanding how the

value_if_trueandvalue_if_falsearguments work is required once you move on to a Nested IF, where one or both of those arguments might be another IF function.For example, it appears that you want the maximum value of the function to be 28. Now let's add another requirement: the minimum value should be 10. Anything between 10 and 28 should return the value in E3. That would look like this:

=IF(E3>28,28,IF(E3<10,10,E3))

You will note that for the

logical_testE3>28, thevalue_if_falseis another IF which "tests" to see if E3 is less than 10. If so, it returns 10, otherwise it returns the value in E3.The main thing to keep in mind is that a Nested IF performs it's calculation from left to right and stops at the first

logical_testthat returns TRUE. In other words, it doesn't determine the result of everylogical_testand then decide which result to use. It merely moves from left to right and as soon as it finds something that is TRUE, it returns thevalue_if_truefor the preceedinglogical_test. If none of thelogical_testsare TRUE, then the entire IF returns the finalvalue_if_false. For that reason, you must make sure that you put yourlogical_testsin the correct order so that it doesn't stop before it should.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Would you use Amazon to buy airline tickets?

Discuss in The Lounge

Poll History