[Solved] N00B help with nested IF/OR formula

Discuss the spreadsheet application

[Solved] N00B help with nested IF/OR formula

Postby Gojira666 » Mon Jul 17, 2017 2:28 pm

Hi, I've zero experience with Calc and a tiny bit on Excel. I therefore desperately need some assistance as the help files are not helpful for a novice.

I have a multiple tab sheet which is data sets by year which is brought together on a summary tab.

I have this code : =IF('2017'.Q246="YES";"XF";"X";IF(OR '2017'.R246="YES";"XL";"X"))) The possible options for the summary cell are x, xl or xf.

The gist is: I want Calc to look at whether there is a 'YES' in the cell Q246 and if so, put 'XF' in the 'summary' tab in cell N1169, but also to check the cell next to it, "r246" and if it has a yes put xf in as the result instead. if neither have a yes the result should be a simple X.

Is this possible?

I appreciate any help!
Last edited by Hagar Delest on Mon Jul 17, 2017 9:53 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.3 on Windows 10
Gojira666
 
Posts: 2
Joined: Mon Jul 17, 2017 2:11 pm

Re: N00B help with nested IF/OR formula

Postby FJCC » Mon Jul 17, 2017 2:42 pm

I think what you want is
Code: Select all   Expand viewCollapse view
=IF('2017'.Q246="YES";"XF";IF('2017'.R246="YES";"XL";"X"))

If Q246 is YES, then return XF. If Q246 is not YES but R246 is YES, return XL. Otherwise return X. There are other ways I could have interpreted your questions. Let me know if I you want some other result.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 5985
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: N00B help with nested IF/OR formula

Postby Gojira666 » Mon Jul 17, 2017 4:46 pm

Fantastic. Thanks so much! :bravo:

Can I ask, if I were to locate an excel 'handbook', would the formulae be identical in style? I could do with swotting up as I'm sure there's loads I can do with the spreadsheet.

FYI it's an annual membership record (maximum of 300 per year)

Oh, you know in Excel you can highlight the columns and click to "subtotal" by your preferred column? I tried that in the summary sheet so I could group each person's different year memberships into one line instead of say, the summary sheet listing each individual entry for each year for that one person. I don't suppose I could press you for info as to how I can find out how to do that? When I attempted it, it didn't do anything at all.
OpenOffice 4.1.3 on Windows 10
Gojira666
 
Posts: 2
Joined: Mon Jul 17, 2017 2:11 pm

Re: N00B help with nested IF/OR formula

Postby Villeroy » Mon Jul 17, 2017 6:26 pm

Click any cell in your list.
menu:Data>Subtotals...
I never use subtotals since they mix my data with formula areas which is a bad design decision.

Same results in a separate table: https://wiki.openoffice.org/wiki/Docume ... /DataPilot [aka pivot table]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24231
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot], morchat and 53 guests