[Solved] =Average problems when cells are empty

[judgesinel]

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.

[Zizi64]

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.))

[judgesinel]

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.

[Villeroy]

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.

[Zizi64]

Please ulpoad a real .ods sample file instead of the picture.

[judgesinel]

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.

[keme]

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.

[judgesinel]

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.

[RusselB]

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.

[keme]

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

[judgesinel]

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.