[Solved] Formula Help?

Discuss the spreadsheet application

[Solved] Formula Help?

Postby Mugs444 » Wed Feb 13, 2019 4:57 pm

Hi there,

This may be very simple, but I don't know how to search for it properly because I can't seem to find the answer.

I want to use a conditional format, but if the value is greater than 0, I'd like the answer to the equation to show, not the equation. Here's what I have now:

=IF(B30-B29<0; "0";IF(B30-B29>0;"=B30-B29")

and what shows in the cell is =B30-B29, but i'd like the answer to show, not the equation...

Thank you!

Mugs
Last edited by Hagar Delest on Thu Feb 14, 2019 9:06 am, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.1
Mac OSX 10.9.5
Mugs444
 
Posts: 4
Joined: Wed Feb 13, 2019 4:51 pm

Re: Formula Help?

Postby RoryOF » Wed Feb 13, 2019 5:03 pm

What happens if B30=B29? Why quotes about "0"? Why quotes about =B30-B29?
Apache OpenOffice 4.1.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 28573
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Formula Help?

Postby John_Ha » Wed Feb 13, 2019 6:58 pm

Search Help with function and learn how to use the Function Wizard.
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6579
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Formula Help?

Postby robleyd » Wed Feb 13, 2019 11:42 pm

Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2570
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Formula Help?

Postby Mugs444 » Thu Feb 14, 2019 12:27 am

RoryOF wrote:What happens if B30=B29? Why quotes about "0"? Why quotes about =B30-B29?


Error 510, thank you for trying!

=IF(B30-B29<0;"0";"=B30-B29") shows =B30-B29 in cell....

=IF(B30-B29<0;"0";=B30-B29) shows Err. 510

I don't know. I've looked on attached links, but am not find much about conditional. Function wizard says

IF Test; Then_Value; Otherwise_Value :shock: :roll:
Last edited by Mugs444 on Thu Feb 14, 2019 12:45 am, edited 1 time in total.
OpenOffice 4.1.1
Mac OSX 10.9.5
Mugs444
 
Posts: 4
Joined: Wed Feb 13, 2019 4:51 pm

Re: Formula Help?

Postby RoryOF » Thu Feb 14, 2019 12:31 am

I meant "what should happen if B30=B29?" This condition is not catered for in your initial attempt.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 28573
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Formula Help?

Postby Mugs444 » Thu Feb 14, 2019 12:54 am

RoryOF wrote:I meant "what should happen if B30=B29?" This condition is not catered for in your initial attempt.


It won't! I'm trying to track my writing. I have a daily goal of 1500 words. I wanted to set up a weekly goal of 10500, so if I have a bad day, I still am pushing for a 100% week. But right now rather than saying something like 40% or whatever it would be, it says -276% because the empty formula spot shows -32714 words. So here is my excel sheet:
Attachments
writingchart.png
OpenOffice 4.1.1
Mac OSX 10.9.5
Mugs444
 
Posts: 4
Joined: Wed Feb 13, 2019 4:51 pm

Re: Formula Help?

Postby RusselB » Thu Feb 14, 2019 1:25 am

=IF(B30-B29<0;"0";=B30-B29) shows Err. 510

The problem with this formula is the second =
Use
Code: Select all   Expand viewCollapse view
=IF(B30-B29<0;"0";B30-B29)
or as an alternative suggestion
Code: Select all   Expand viewCollapse view
=max(0;B30-B29)
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4931
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Formula Help?

Postby Mugs444 » Thu Feb 14, 2019 5:52 am

The second option worked perfectly! Very simple and clean, thank you RusselB!
OpenOffice 4.1.1
Mac OSX 10.9.5
Mugs444
 
Posts: 4
Joined: Wed Feb 13, 2019 4:51 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 20 guests