3

I am trying to get the average value of a column (column C "Profit Margin" in the screenshot) in Google Sheets. For some reason, I get a #DIV/0 error when trying to use any sort of range functions like SUM, AVERAGE, COUNT, etc on this column:

enter image description here

I feel like it is related to the fact that column C was generated using ARRAYFORMULA, but I'm not sure...

Here is a link to a copy of the spreadsheet if you want to look at the data: BuyBack Example Spreadsheet

Improve this question
2
  • UPDATE: Sorry, the sharing permissions weren't correctly set on the link I shared. The document should be publicly accessible now.
    – Jesse T.
    Commented Oct 9, 2019 at 22:58
  • Please check the answer and if it solves your question accept it so others can benefit as well. If not, let us know.
    – marikamitsos
    Commented Oct 10, 2019 at 5:22

2 Answers 2

Reset to default
3

(In your example spreadsheet you have no formulas)

Most likely your formula calculated results are given in TEXT format, so AVERAGE -which needs numbers to make calculations- can NOT give any results and you end up with #DIV/0!.

A quick and easy fix:
To change text cells to number cells, select your range (C2:C) and choose a number format for it by going to the top menu, Format --> Number.

Your other alternative is to use the function VALUE within the Arrayformula so as to get the results formatted as numbers.

EDIT (following OP's sharing the sheet)

Your problem lies on rows 1496-1499.

In your buys sheet, rows 1496-1499, in column M (Price) the value is 0.
This causes your formula =ARRAYFORMULA(1- B2:B3796/A2:A3796) for Profit margin in Sheet1in column Cto give #DIV/0! which in turn gives #DIV/0! in column E (Average margin).

To solve this (as mentioned above):

  1. Change your formula for Average margin to =IFERROR(average(C2:C), "CHECK your buys")
  2. Check your buys

Extra tip: As an additional step, use conditional formatting in your buys sheet, to easily spot the issues.

Improve this answer
1
  • Sorry, I copied from the original document because I can't share it since it has private -customer data in it. The data is not formatted as text. It was formatted as number (I also tried converting it to %) Neither worked. The $ fields are also numeric, not text btw.
    – Jesse T.
    Commented Oct 10, 2019 at 18:15
0

The problem is in the formulation:

=ARRAYFORMULA(1- IFERROR(B2:B3796/A2:A3796,0))

Add validation in division!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.