2

I have two columns (Google Sheets) of numbers similar to:

10.453   12.178  
12.654   0

The third column is:

  • if number higher than 0 exists in both columns, display an average of the two
  • else just display the one figure entered (in either column)
2

Another way of doing it, is by using the AVERAGEIF formula.

Formula

=AVERAGEIF(A1:B1, ">0")

Screenshot

enter image description here

  • I had no idea this function existed. By far the least complicated out of the two – Kade Apr 9 '15 at 18:28
  • That is brilliant! pre-race it shows a div/0 error if both are 0 but that's ok :) This one also works across google sheets/excel/open office. Thanks!! – Androo Apr 9 '15 at 22:45
1

Here's a function to do thisif a1>0 and b1>0 then average a1 and b1 else add a1 and b1

=if(AND(A1>0,B1>0),AVERAGE(A1,B1),add(A1,B1))  

Obviously you'll have to plug in your own cells for a1 and b1
You can find all the google functions documentation here

  • Thanks, that's half-way there... if one column has no entry then it'd just display the one number that is entered? at the moment it shows nothing if one column is blank – Androo Apr 1 '15 at 22:05
  • Sorry about that, I misread the question. Included in the edit on the main post add(a1,b1) will return as '0' when both are blank and when one cell is full it will return the cell and when both it will execute the average – Kade Apr 1 '15 at 22:15
  • You are amazing, thank you so much - have a great day! – Androo Apr 1 '15 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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