Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question about syntax and nested IF-functions

Is the following formula a valid formula?

=IF B37>=150,">99.9",IFB37<=5,"<0.1",SUMC100:C154


If this is a valid formula, what is the correct syntax?


I'm trying to sum a column of numbers. Some cells at the top and bottom of the column are > or < 1, which the Numbers cannot add. In those cases, i'm trying to have Numbers treat the expression like text. In the rest of the cases, it just SUMs the column.


Thanks,

Avi

MacBook Pro (15-inch Mid 2010), OS X Yosemite (10.10.5)

Posted on Oct 6, 2015 7:54 PM

Reply
5 replies

Oct 6, 2015 8:07 PM in response to avifromthornhill

to see the proper syntax for any function, type the equal sign in a cell, then use function browser on the right. You can filter the function or browse the list.


the syntax for IF:

The IF function returns one of two values, depending on whether a specified expression evaluates to a boolean value of TRUE or FALSE.

IF(if-expression, if-true, if-false)

  • if-expression: A logical expression. if-expression can contain anything as long as the expression can be evaluated as a boolean value. If the expression evaluates to a number, 0 is considered to be FALSE, and any other number is considered to be TRUE.
  • if-true: The value returned if if-expression is TRUE. if-true can contain any value. If if-trueis omitted (there’s a comma, but no value) and if-expression evaluates to TRUE, IF will return 0.
  • if-false: An optional argument specifying the value returned if if-expression is FALSE. if-false can contain any value. If if-false is omitted (there’s a comma, but no value) and if-expression evaluates to FALSE, IF will return 0. If if-false is entirely omitted (there’s no comma after if-true) and if-expressionevaluates to FALSE, IF will return FALSE.

Notes

Either or both if-true and if-false can contain additional IF functions (often called nested IF functions).




there is a function IF(), SUM(), AND() which can all be useful. Ultimately I think you will want to use the function SUMIFS() which will sum values from a range if all conditions are met. look at the syntax for sumifs(). SUMIFS() is SUM IF with more than one condition.


sum if a value is less than a threshold and the value is greater than another threshold.

Oct 7, 2015 7:54 AM in response to avifromthornhill

avifromthornhill wrote:


Is the following formula a valid formula?

=IF B37>=150,">99.9",IFB37<=5,"<0.1",SUMC100:C154


If this is a valid formula, what is the correct syntax?





The syntax would be:


=IF(B37≥150,">99.9",IF(B37≤5,"<0.1",SUM(C100:C154)))


This looks in cell B37. If the value there is 150 or greater it returns >99.9. If the value there is 5 or less it returns <0.1. Otherwise it returns the sum of cells C100:C154.


Is that what you want it to do?


SG

Question about syntax and nested IF-functions

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.