## [Solved] =Average problems when cells are empty

### [Solved] =Average problems when cells are empty

I apologise in advance as I have seen multiple responses to using this formula but none are working for me in a very simple scenario.

I have a short column of figures, but one cell in the column could be blank, it has a simple formula in the blank cell, like =SUM(H45/F45) that can't be completed because one of the cells F45 is blank. So the cell where this formula is, is blank At the end of the column, I have =AVERAGE (J38:J45) for example.

The problem is it does not average the existing or showing figures and still divides the total of the column by every cell including the blank cell, thus giving me an incorrect average of the figures in the column.

I hope this is clear enough for someone to provide a solution and thanks in advance if you can.
Last edited by judgesinel on Sun Dec 01, 2019 12:10 pm, edited 2 times in total.
judgesinel

Posts: 21
Joined: Sun Nov 25, 2007 9:53 pm

### Re: =Average problems

it has a simple formula in the blank cell, like =SUM(H45/F45) that can't be completed because one of the cells F45 is blank.

Use a condition:
Code: Select all   Expand viewCollapse view
`=IF(ISBLANK(F45);"";H45/F45)`

or
Code: Select all   Expand viewCollapse view
`=IF(ISBLANK(F45);0;H45/F45)`

(wich is more relevant in your case)

((The SUM function is unnecessary when you want to make a division with two numbers.))
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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.

Zizi64
Volunteer

Posts: 9703
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: =Average problems

I really appreciate the reply and assistance but I'm obviously not explaining this properly, apologies.

I have a column for say "units per day" that get calculated at various times during a period. I only have 8 rows per period and sometimes one of the rows is not populated. In this un-populated cell, there is a formula =SUM(H45/F45) for example. I only mention this because this may be a problem when the column of 8 rows is averaged and there is no calculated figure in the cell. So normally, if all 8 rows are populated the average cell would look like this =SUM(J38:J45)/8 giving me the average of the 8 rows. So the problem arises when the one cell is not calculated as =average still divides by the 8 cells when only say 7 cells have a calculated figure in them.

judgesinel

Posts: 21
Joined: Sun Nov 25, 2007 9:53 pm

### Re: =Average problems

The AVERAGE function ignores any text, so =IF(ISBLANK(F45);"";H45/F45) might be a solution because "" is a text with zero characters. You may use any other text such as "NA" or "Null". AVERAGE won't count it.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 29124
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: =Average problems

Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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.

Zizi64
Volunteer

Posts: 9703
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: =Average problems

I have attached the spreadsheet, I'm a little embarrassed about it as spreadsheets are not something I use frequently and I wanted to track our electricity usage and cost. I'm happy for any recommendations or changes to make it look decent or make it more functional.
Attachments
judgesinel

Posts: 21
Joined: Sun Nov 25, 2007 9:53 pm

### Re: =Average problems

Two discrepancies:
• You mention calculating with "=average", and how it counts all 8 cells even if one is blank. Your formulas in the averaging row do not use the AVERAGE() function, but a "hardcoded averaging" with a constant divisor - 8 - instead of counting actual entries.
• The number format of the "blank" cells is set to display negative values as blank. The cell value is still a negative number. Format governs how cell content is displayed. The content (e.g. formula result) exists independently of formatting, and it it this content which is used in further calculations. To exclude negative values from the averaging, you need to use the "" in a cell formula, not in a format spec.

keme
Volunteer

Posts: 3400
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: =Average problems

keme, forgive me but it's like pulling teeth getting an answer that solves my problem. I do not use spreadsheets much and if I do it is usually for simple calculations that I recall from my Quatro Pro days. The spreadsheet I attached had the hardcoded average calculation in it because the =average was not working for me. I understand from your reply that the formating does not alter the content, got it. I did this to try and make it look neat until it had a meaningful figure in it. So, what do you suggest the formula should be, please? By the way, I see your from Norway, I was in Oslo this time last year, what an amazing city, loved it especially the Viking Ship Museum.
judgesinel

Posts: 21
Joined: Sun Nov 25, 2007 9:53 pm

### Re: =Average problems

Work on getting the correct results, then worry about making it look nice.
Doing it the other way around can (and usually does) make more work in the long run.
I have mode modifications to your spreadsheet that make better/correct use of the various functions and processes.
The modified version is attached.
Review the spreadsheet and let us know about any discrepancies between what you see and what you expect to see. Please make sure it is clear which is which.
Attachments
Calc 100160 RKB 1.ods
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.

RusselB
Moderator

Posts: 6276
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: =Average problems

judgesinel wrote:keme, forgive me but it's like pulling teeth getting an answer that solves my problem. I do not use spreadsheets much and if I do it is usually for simple calculations that I recall from my Quatro Pro days. The spreadsheet I attached had the hardcoded average calculation in it because the =average was not working for me. I understand from your reply that the formating does not alter the content, got it. I did this to try and make it look neat until it had a meaningful figure in it. [...]

Yes that is fine, no offense taken, and none intended from me either in pointing out "discrepancies".

I refrained from making any explicit suggestions for change because I didn't know for sure whether the "invisible negatives" had a useful role to play in your calculation.

We also try to convey information before we provide premade solutions, so you hopefully gain some skills in the process. So, I hesitated to post an adjusted spreadsheet file, awaiting a response that you either solved it yourself, or that you specify precisely the context and requirements for your spreadsheet, for us to suggest a solution.

RussellB beat me to that final response

keme
Volunteer

Posts: 3400
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: =Average problems when cells are empty

Awesome guys, from that supplied, I have been able to understand and clean up my spreadsheet nicely. I appreciate the help. Have a good Christmas.
judgesinel

Posts: 21
Joined: Sun Nov 25, 2007 9:53 pm