OO Calc question
 

I am trying to do an if/then formula and can't get to work.
C4 and D4 are input numbers. I want E4 to be the average of these, but I want it to be a blank cell or show --- if there is nothing in cell C4.
What happens if I clear C4 is the value of D4. If I clear both cells, I get an error /0 result.

What am I doing wrong?

=IF(C4>0,AVERAGE(C4:D4),"---")

Works for me!

I can get it if I put a 0 in C4, but not if I leave it blank.
Are you using Libre OO?

Thanks

I am using Microsoft Office Professional Plus 2013.

Libreoffice:

http://www.paceadvantage.com/forum/b...e-bf191063a9f7https://i.ibb.co/n3VP9JC/libreoffice.png

A blank is not a zero although OO should figure that out. Maybe put the main If inside another that is something like If(C4<>"",If(C4>0 etc.

There is a configurable option in LibreOffice Calc called "EmptyStringAsZero." It is likely that OpenOffice Calc has the same (or a similar) option.

Here is a relevant post.

This should work:

=IF(OR(C4<=0,C4=""),"---",AVERAGE(C4,D4))

So far, nothing works, even the formula shown above for Libre.
THANKS for the help guys.

I have had my fill of OO.
Both versions are just inferior to Excel.
Office 365 is not that expensive, time to ditch thee free shit.

I just stumbled onto the secret. :rolleyes:

If I clear both C and D columns, E goes blank, which is what I wanted.
Everything worked then.

Congrats!!

There's nothing wrong in looking for the cheapest way out....:headbanger:

Not sure what happened, worked fine for me. See attached image straight from CALC with all three cases...value, 0, blank.

I discovered today that most everything works if I actually delete the cell contents, not just clear it by hitting the spacebar.

That makes sense. You know things like that keep me up at night when it didn't work for you! :lol:

I guess making it a space isn't the same as an empty string. Maybe adjust cj's code to something like
=IF(OR(C4<=0,TRIM(C4)=""),"---",AVERAGE(C4,D4))