IF() statement

Discuss the spreadsheet application

IF() statement

Postby gverhage » Tue Jan 22, 2019 3:51 pm

I can't figure what is wrong with this IF statement

M26 reads

=IF(K56>500;=SUM(K56-500);0) Gives ERR:510

K56=1579.08

M26 should read 1079.08

Please look it over and let me know what I am doing wrong. Thanks
OpenOffice 4 on windows 10
gverhage
 
Posts: 2
Joined: Thu Mar 22, 2018 5:41 am

Re: if statement

Postby RoryOF » Tue Jan 22, 2019 3:53 pm

Why =Sum(K56-500)? Would not K56-500 be better?
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: 28546
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: if statement

Postby RusselB » Tue Jan 22, 2019 4:36 pm

Experience with this type of problem leads me to believe it's due to the second = sign in the formula.
I don't know why OpenOffice doesn't like seeing more than one = sign in a single formula, but it doesn't.
I also agree with Rory about using the simpler K6-500.
So, I'm suggesting using
Code: Select all   Expand viewCollapse view
=if(K56>500;K56-500;0)
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: 4919
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: if statement

Postby John_Ha » Tue Jan 22, 2019 7:10 pm

gverhage wrote:=IF(K56>500;=SUM(K56-500);0) Gives ERR:510

Take out the "= " in front of SUM.

" = " is only used when defining a formula in a cell where it tells Calc to treat what is typed as a formula and not as a text string.

SUM is a function and it does not require " = " in front of it.

That being said your use of SUM(K56-500) is extremely non standard - you are saying "add the following numbers, where Number 1 is (K56-500), and there are no Number 2, Number 3 etc", so you are not adding any numbers! Note that in SUM(x;y;z) the numbers to be added must be separated by semi-colons. The simplest form of the formula is therefore =IF(K56>500;(K56-500);0) where the brackets round (K56-500) are advisable for clarity but not mandatory.

Clipboard01.gif

You will find much useful information in the User Guides, the Writer, Base and Calc Tutorials and the AOO Frequently Asked Questions. May I suggest you bookmark the pages.

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.
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: 6567
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK


Return to Calc

Who is online

Users browsing this forum: No registered users and 21 guests