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!
[Solved] N00B help with nested IF/OR formula
[Solved] N00B help with nested IF/OR formula
Last edited by Hagar Delest on Mon Jul 17, 2017 9:53 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
OpenOffice 4.1.3 on Windows 10
Re: N00B help with nested IF/OR formula
I think what you want is
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.
Code: Select all
=IF('2017'.Q246="YES";"XF";IF('2017'.R246="YES";"XL";"X"))
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: N00B help with nested IF/OR formula
Fantastic. Thanks so much!
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.
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
Re: N00B help with nested IF/OR formula
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]
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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice