All cells with '0' are to be ignored, and the value is to be calculated with any number higher than this.
The attached photo is my attempt, clearly wrong because it returns a syntax error.
Thanks in advance :)
MacBook Pro 15″, macOS 12.5
Hi George,
The syntax error is a pretty simple one, but it is a quirk of AverageIF and a few other functions.
Your AVERAGEIF formula could look like the second formula shown below the table. Note the quotation marks enclosing >0.
The 'quirk' referred to above is that AVERAGEIF requires the 'comparison value' ( >0 ) to be presented as TEXT.
Placing that value in quotes fulfils that requirement, as can be seen in cell D9, the cell containing that formula.
The shorter formula is in B9. I included it to demonstrate two things:
One property of Header and Footer rows is that for many functions a 'whole column' cell reference will include only the cells that are not Header row cells and not Footer row cells.
In this case, that property permits using a full column reference ( B in this formula) to specify the cells to be included (B2:B8) in the AVERAGE.
Note that this property also applies to AVERAGEIF, as seen in the AVERAGEIF formula used in column D.
2 The AVERAGE function ignores empty cells (and cells containing text values), but includes cells containing numbers.
Revise your data recording to match this edited version of what you asked in the title: "Average with missing values", and you no longer need to use "AVERAGEIF".
Note: Replace the commas ( , ) in the formulas with semi colons ( ; ) to fit the convention in your region.
Regards,
Barry
Hi George,
The syntax error is a pretty simple one, but it is a quirk of AverageIF and a few other functions.
Your AVERAGEIF formula could look like the second formula shown below the table. Note the quotation marks enclosing >0.
The 'quirk' referred to above is that AVERAGEIF requires the 'comparison value' ( >0 ) to be presented as TEXT.
Placing that value in quotes fulfils that requirement, as can be seen in cell D9, the cell containing that formula.
The shorter formula is in B9. I included it to demonstrate two things:
One property of Header and Footer rows is that for many functions a 'whole column' cell reference will include only the cells that are not Header row cells and not Footer row cells.
In this case, that property permits using a full column reference ( B in this formula) to specify the cells to be included (B2:B8) in the AVERAGE.
Note that this property also applies to AVERAGEIF, as seen in the AVERAGEIF formula used in column D.
2 The AVERAGE function ignores empty cells (and cells containing text values), but includes cells containing numbers.
Revise your data recording to match this edited version of what you asked in the title: "Average with missing values", and you no longer need to use "AVERAGEIF".
Note: Replace the commas ( , ) in the formulas with semi colons ( ; ) to fit the convention in your region.
Regards,
Barry
TheChristianGeorge wrote:
wrong because it returns a syntax error.
The number values in the cells should be right-aligned by default. In your screenshot they do not appear right-aligned.
This suggests that your machine (or Numbers, via File > Advanced > Language and Region ... in the menu) may be set to expect number values with . as the decimal separator rather than the , shown in your screenshot.
That could cause an error even if you enclose the >0 in "".
Not directly related to the problem, but keep in mind that in Numbers it is a good idea put your formulas at the bottom in a Footer Row. That way, instead of having to refer to a cell range the way you have to in Excel, you can simply refer to the entire column, like this:
When your machine, or Numbers, is set to a region with , as a decimal separator, then the same thing would look like this:
SG
Thank you so much! Your answer solved everything and was very clear. You're a champion!
I've put both methods in different spreadsheets so that in that in the future I can refer back to multiple methods of calculating the average :)
Forever indebted for your advice,
George
Hey SG!
I initially did decimal points and found out quickly enough that comma separators were required to keep it formatted as number cells and not strings :)
The numbers were all centered at my behest, just for the asthetics lol.
Thanks so much for your advice! I set it in another spreadsheet so that for future use I can always refer back to it.
Warm regards,
George
'AverageIf' with missing values.
