 |
|
09-13-2021, 04:16 PM
|
#1
|
|
The Voice of Reason!
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,787
|
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?
__________________
Who does the Racing Form Detective like in this one?
|
|
|
|
09-13-2021, 04:40 PM
|
#2
|
|
Registered User
Join Date: Dec 2020
Posts: 69
|
=IF(C4>0,AVERAGE(C4  4),"---")
Works for me!
Last edited by Kbatch57; 09-13-2021 at 04:45 PM.
|
|
|
|
|
09-13-2021, 07:20 PM
|
#3
|
|
The Voice of Reason!
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,787
|
I can get it if I put a 0 in C4, but not if I leave it blank.
Are you using Libre OO?
Thanks
__________________
Who does the Racing Form Detective like in this one?
|
|
|
|
|
09-13-2021, 07:30 PM
|
#4
|
|
Registered User
Join Date: Dec 2020
Posts: 69
|
Quote:
Originally Posted by Tom
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.
|
|
|
|
|
09-13-2021, 08:54 PM
|
#5
|
|
Registered User
Join Date: Jan 2007
Location: Los Angeles
Posts: 492
|
Libreoffice:
 
Last edited by tupper; 09-13-2021 at 09:01 PM.
|
|
|
|
|
09-14-2021, 10:08 AM
|
#6
|
|
dGnr8
Join Date: Aug 2003
Location: Niagara, Ontario
Posts: 3,023
|
Quote:
Originally Posted by Tom
I can get it if I put a 0 in C4, but not if I leave it blank.
|
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.
__________________
.
The great menace to progress is not ignorance but the illusion of knowledge - Daniel J. Boorstin
The takers get the honey, the givers sing the blues - Robin Trower, Too Rolling Stoned - 1974
|
|
|
|
|
09-14-2021, 11:11 AM
|
#7
|
|
Registered User
Join Date: Jan 2007
Location: Los Angeles
Posts: 492
|
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.
|
|
|
|
|
09-14-2021, 05:11 PM
|
#8
|
|
@TimeformUSfigs
Join Date: Jan 2002
Location: Moore, OK
Posts: 46,827
|
This should work:
=IF(OR(C4<=0,C4=""),"---",AVERAGE(C4,D4))
|
|
|
|
|
09-14-2021, 10:14 PM
|
#9
|
|
The Voice of Reason!
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,787
|
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.
__________________
Who does the Racing Form Detective like in this one?
|
|
|
|
|
09-14-2021, 10:47 PM
|
#10
|
|
The Voice of Reason!
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,787
|
I just stumbled onto the secret. 
If I clear both C and D columns, E goes blank, which is what I wanted.
Everything worked then.
__________________
Who does the Racing Form Detective like in this one?
|
|
|
|
|
09-14-2021, 11:19 PM
|
#11
|
|
Veteran
Join Date: May 2021
Location: NYC
Posts: 1,554
|
Quote:
Originally Posted by Tom
I just stumbled onto the secret.
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.... 
|
|
|
|
|
09-15-2021, 11:23 AM
|
#12
|
|
@TimeformUSfigs
Join Date: Jan 2002
Location: Moore, OK
Posts: 46,827
|
Quote:
Originally Posted by Tom
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.
|
Not sure what happened, worked fine for me. See attached image straight from CALC with all three cases...value, 0, blank.
|
|
|
|
|
09-16-2021, 01:44 PM
|
#13
|
|
The Voice of Reason!
Join Date: Mar 2001
Location: Canandaigua, New york
Posts: 112,787
|
I discovered today that most everything works if I actually delete the cell contents, not just clear it by hitting the spacebar.
__________________
Who does the Racing Form Detective like in this one?
|
|
|
|
|
09-16-2021, 05:02 PM
|
#14
|
|
@TimeformUSfigs
Join Date: Jan 2002
Location: Moore, OK
Posts: 46,827
|
Quote:
Originally Posted by Tom
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! 
|
|
|
|
|
09-17-2021, 08:21 AM
|
#15
|
|
dGnr8
Join Date: Aug 2003
Location: Niagara, Ontario
Posts: 3,023
|
Quote:
Originally Posted by Tom
I discovered today that most everything works if I actually delete the cell contents, not just clear it by hitting the spacebar.
|
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))
__________________
.
The great menace to progress is not ignorance but the illusion of knowledge - Daniel J. Boorstin
The takers get the honey, the givers sing the blues - Robin Trower, Too Rolling Stoned - 1974
|
|
|
|
|
|
| Thread Tools |
|
|
| Rate This Thread |
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
