[Solved] Need help with my formula

Discuss the spreadsheet application

[Solved] Need help with my formula

Postby edurant03 » Thu Jul 12, 2018 2:24 am

I have two sheets, the first I enter data into a cell say "B2" called "Entry Form", that gets calculated into another cell "I12", the results then are displayed in a cell on the second sheet which has the formula of ='Entry Form' .I12 giving me my results.

Now my objective is to set an IF,OR,ELSE etc function that would display "ND" if the ='Entry Form' .I12 results were equal to 0 and if the results were greater than 0 I need the cell to display as it normally would with the data from ='Entry Form' .I12

I have figured out the formula to populate =IF('Entry Form' .I12=0; "ND") so that it will populate ND but I can't figure out how to have the normal values display as they would if not 0

Is there such a way to pull actual data from another sheet and have and IF function also? Thank you in advance!
Last edited by robleyd on Sat Jul 14, 2018 3:14 am, edited 1 time in total.
Reason: Tagged [Solved] [robleyd, Moderator]
END OpenOffice 4.1.3
edurant03
 
Posts: 4
Joined: Thu Jul 12, 2018 2:06 am

Re: Need help with my formula

Postby robleyd » Thu Jul 12, 2018 2:51 am

G'day and welcome to the forum.

The syntax for the IF function is:
Code: Select all   Expand viewCollapse view
IF(Test; ThenValue; OtherwiseValue)


You have =IF('Entry Form' .I12=0; "ND") where 'Entry Form' .I12=0 is the Test and "ND" is the ThenValue. You simply need to add the third argument to the IF function; that is, what you want to display for the OtherwiseValue when 'Entry Form' .I12 is not zero.

You haven't mentioned if you need to deal differently with a value less than zero.
Cheers
David
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.5 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1971
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Need help with my formula

Postby edurant03 » Thu Jul 12, 2018 3:02 am

Hi thank you for responding, I did that but the problem I am having is it displays the actual wording "'Entry Form' .I12" instead if displaying the data or actual values from the entry form sheet. There will not be values less then 0, just 0 and greater.
END OpenOffice 4.1.3
edurant03
 
Posts: 4
Joined: Thu Jul 12, 2018 2:06 am

Re: Need help with my formula

Postby robleyd » Thu Jul 12, 2018 3:09 am

Can you upload a sample spreadsheet file showing the problem?

[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire and post the link here. The link also contains information on how to anonymise your document if it contains confidential information.
Cheers
David
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.5 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1971
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Need help with my formula

Postby RusselB » Thu Jul 12, 2018 4:42 am

edurant03 wrote:I have figured out the formula to populate =IF('Entry Form' .I12=0; "ND") so that it will populate ND but I can't figure out how to have the normal values display as they would if not 0

Simply repeat the 'Entry Form' .I12 as the 3rd parameter in your IF
edurant03 wrote:Is there such a way to pull actual data from another sheet and have and IF function also? Thank you in advance!

Yes you can... in fact you are starting to do that already by having a named sheet. You just have to remember to specify the sheet name, which you have done in your formula by referencing the sheet named Entry Form
OpenOffice 4.1.4 and LibreOffice 5.2.7.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: 4529
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Need help with my formula

Postby edurant03 » Sat Jul 14, 2018 12:33 am

Could you give me an example? Would I be entering something along the lines of =IF('Entry Form' .I12=0; "ND"); IF('Entry Form' .I12>0; "'Entry Form' .I12")

I did that before and it would give me the actual statement Entry Form where my data would be.
END OpenOffice 4.1.3
edurant03
 
Posts: 4
Joined: Thu Jul 12, 2018 2:06 am

Re: Need help with my formula

Postby RusselB » Sat Jul 14, 2018 12:48 am

Code: Select all   Expand viewCollapse view
=IF('Entry Form' .I12=0; "ND";'Entry Form' .I12)
OpenOffice 4.1.4 and LibreOffice 5.2.7.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: 4529
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Need help with my formula

Postby edurant03 » Sat Jul 14, 2018 12:52 am

It worked!!! So simple lol I tried that first but had the "" around entry form so it didnt work and I thought there was more to it. OMG Thank you!
END OpenOffice 4.1.3
edurant03
 
Posts: 4
Joined: Thu Jul 12, 2018 2:06 am

Re: [Solved] Need help with my formula

Postby Zizi64 » Sat Jul 14, 2018 6:29 am

I tried that first but had the "" around entry form so it didnt work and I thought there was more to it.

You has used space character in the name of the sheet.
You need apostrophes around the sheet name only in such special cases.

Do not use white spaces in a name of a sheet. Use _ character instead.
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.7 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.0 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7192
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests