[Solved] Average non-empty cells

Discuss the spreadsheet application
Post Reply
yoyopoc
Posts: 7
Joined: Wed Apr 17, 2019 12:59 pm

[Solved] Average non-empty cells

Post by yoyopoc »

Hi Everyone,
Just getting going with spreadsheets on my new Linux machine, got so far but lack of mathematical mind hampering my progress (i'm an artist and shouldn't really be wasting your time but hey ho)
I'm summing a column like so =SUMIF(C2:C1000,">0") so no cells without values are ignored but I need to divide said SUM by the number of cells in said column but ignore the blank ones.
At the moment there are only 22 cells with values in the column so used =SUM(C1:C1000)/22 to get a value but as they will grow, how can I get the division to work as the number of cells grow with values?
Thanks for any help :oops:
Last edited by MrProgrammer on Sat Oct 02, 2021 3:13 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OpenOffice 4.1.1, MAC OS 10.13.6
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Yet another beginner question

Post by FJCC »

If you want the average, use the AVERAGE() function, which ignores blank cells.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Yet another beginner question

Post by RusselB »

On a side note, your SUMIF will sum the positive numbers in the specified range, but what if you have a negative number?
If you just use =SUM, then blank cells will be treated as 0 for the SUM
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
yoyopoc
Posts: 7
Joined: Wed Apr 17, 2019 12:59 pm

Re: Yet another beginner question

Post by yoyopoc »

Hi,
There will never be negative numbers in the data so that is no problem.
I've tried the AVERAGE and it shows a different number than the division? really just want a way for the =SUM(C1:C1000)/22 where 22 is the number of cells with data to automatically find and use the number of cells that contain data automatically as data is entered into the sais cells?
Thanks for your help :?
OpenOffice 4.1.1, MAC OS 10.13.6
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Yet another beginner question

Post by RusselB »

Do any of the cells in the range contain the number 0?
If so, then AVERAGE is including those cells, but your manual formula does not.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Yet another beginner question

Post by Villeroy »

RusselB wrote:I've tried the AVERAGE and it shows a different number than the division?
In this case, the AVERAGE function is right and you are wrong.
We don't see what you can see. Upload some document showing the issue: [Forum] How to attach a document here
COUNT(cells) counts the numbers in cells.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Yet another beginner question

Post by Zizi64 »

and what about the functions

COUNTIF()?
AVERAGEIF()?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Post Reply