Forum Discussion

Copper Contributor

Dec 26, 2022

Solved

Mean value calculation in Excel

I want to add 5 cells and calculate the mean value if the individual cell is >0. The fifth cell must be added with twice the value and the average value must then be calculated by dividing the total by 6 instead of 5. What is the formula for this?

Formulas and Functions

HansVogelaar
Dec 27, 2022
robertwgmeinergmail

Ich sehe:

13 Replies

HansVogelaar
MVP
Dec 26, 2022
Let's say the cells are F6:F10.

=(SUMIF(F6:F10,">0")+F10)/(COUNTIF(F6:F10,">0")+(F10>0))
- robertwgmeinergmail
  Copper Contributor
  Dec 26, 2022
  HansVogelaar
  
  Hi Hans
  Thank you for your reply. Unfortunately the formula does not work
  Best regards
  Robert
  - HansVogelaar
    MVP
    Dec 27, 2022
    robertwgmeinergmail
    
    Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?