[Solved] =Average problems when cells are empty

Discuss the spreadsheet application

[Solved] =Average problems when cells are empty

Postby judgesinel » Tue Nov 26, 2019 5:51 pm

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: 19
Joined: Sun Nov 25, 2007 9:53 pm

Re: =Average problems

Postby Zizi64 » Tue Nov 26, 2019 6:16 pm

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.0; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8752
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: =Average problems

Postby judgesinel » Wed Nov 27, 2019 5:02 pm

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.

Average spreadsheet.jpg
judgesinel
 
Posts: 19
Joined: Sun Nov 25, 2007 9:53 pm

Re: =Average problems

Postby Villeroy » Wed Nov 27, 2019 6:23 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 27700
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: =Average problems

Postby Zizi64 » Wed Nov 27, 2019 6:27 pm

Please ulpoad a real .ods sample file instead of the picture.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8752
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: =Average problems

Postby judgesinel » Thu Nov 28, 2019 9:42 am

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
sample_spreadsheet.ods
(14.53 KiB) Downloaded 14 times
judgesinel
 
Posts: 19
Joined: Sun Nov 25, 2007 9:53 pm

Re: =Average problems

Postby keme » Thu Nov 28, 2019 10:37 am

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.
User avatar
keme
Volunteer
 
Posts: 3295
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: =Average problems

Postby judgesinel » Thu Nov 28, 2019 5:55 pm

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: 19
Joined: Sun Nov 25, 2007 9:53 pm

Re: =Average problems

Postby RusselB » Thu Nov 28, 2019 10:21 pm

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
(14.38 KiB) Downloaded 13 times
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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
RusselB
Moderator
 
Posts: 5848
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: =Average problems

Postby keme » Fri Nov 29, 2019 12:37 pm

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 :super:
User avatar
keme
Volunteer
 
Posts: 3295
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: =Average problems when cells are empty

Postby judgesinel » Sun Dec 01, 2019 12:07 pm

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: 19
Joined: Sun Nov 25, 2007 9:53 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests