Solved How to write formula

February 5, 2016 at 11:41:10
Specs: Windows 7
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)


See More: How to write formula

Report •


✔ Best Answer
February 6, 2016 at 10:06:04
That is exactly what my suggestion does.

=IF(E3<28,E3,28)

All you did was reverse the logical_test and then swap the value_if_true and value_if_false.

You will note that with my suggestion, when E3 = 28, the logical_test will return FALSE since 28 is not less than 28. When the logical_test returns FALSE, the value_if_false is 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_true and value_if_false arguments 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_test E3>28, the value_if_false is 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_test that returns TRUE. In other words, it doesn't determine the result of every logical_test and 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 the value_if_true for the preceeding logical_test. If none of the logical_tests are TRUE, then the entire IF returns the final value_if_false. For that reason, you must make sure that you put your logical_tests in 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.



#1
February 5, 2016 at 12:10:58
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 E3

Any help is very appreciated

message edited by Basecalc


Report •

#2
February 5, 2016 at 13:59:26
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.


Nigel

Wind slow


Report •

#3
February 5, 2016 at 14:23:15
Technically, it's...

=IF(logical_test, value_if_true, value_if_false)

The logical_test returns TRUE or FALSE, thus the IF returns either the value_if_true or value_if_false.


message edited by DerbyDad03


Report •

Related Solutions

#4
February 5, 2016 at 15:56:06
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


Report •

#5
February 5, 2016 at 21:10:49
=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.


Report •

#6
February 5, 2016 at 22:19:52
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


Report •

#7
February 6, 2016 at 10:06:04
✔ Best Answer
That is exactly what my suggestion does.

=IF(E3<28,E3,28)

All you did was reverse the logical_test and then swap the value_if_true and value_if_false.

You will note that with my suggestion, when E3 = 28, the logical_test will return FALSE since 28 is not less than 28. When the logical_test returns FALSE, the value_if_false is 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_true and value_if_false arguments 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_test E3>28, the value_if_false is 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_test that returns TRUE. In other words, it doesn't determine the result of every logical_test and 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 the value_if_true for the preceeding logical_test. If none of the logical_tests are TRUE, then the entire IF returns the final value_if_false. For that reason, you must make sure that you put your logical_tests in 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.


Report •

Ask Question