how do the formulas work in second sheet

[LindaPro]

My husband has inherited a spreadsheet that has quite a bit of “stuff” I am not familiar with. He knows virtually nothing about using calc and has turned it over to me. I have figured out how the drop down list box works and know that the formulas contained in the second spread sheet are using data from the first spread sheet, but I don’t know how to interpret the formula in the second spreadsheet. I wondered is someone could take a look and provide some answers.

These formulas in the second spreadsheet: =IF($Groups.G7="","",$Groups.G7)
=IF(B5="","",IF(A5=3,IF(H4+18<=B5,"…",IF(H4<=B5,".","")),IF(6+18<=B5,"…",IF(6<=B5,".",""))))

I think I don’t understand what the “,” is doing. I am attaching the spreadsheet in hopes that some kind and knowledgeable person might help me understand what these formulas are doing. Thanks for any help.scorecard - Wizards - incl hybrid - Apr 29th.ods

[LindaPro]

Thanks for the prompt replies. A follow-up, maybe I should post separately: this spreadsheet prints golf scorecards. There is a condition where a double underline prints for some of the holes. Do you know where I would look to find this?

[anon73440385]

There is a condition where a double underline prints

No - there is no Conditional Formatting within your sheet (See Format -> Conditional -> Manage... per sheet) and this would be the only way to modify the format of a cell (Formulas - like using IF() -in general cannot change the appearance of a cell [neglecting the fact there is a STYLE() function, which isn’t used in your sheet anyway]). Hence the conclusion is: The “underlines” are Doube Thin-Style cell borders created by direct formatting - see Right click on a cell (e.g.H5 on sheet Card 1 and 2) and Format Cells -> Tab: Borders

[LindaPro]

Thank you. It took me a little while to “see” how this worked. Your explanation is very clear.

[robleyd]

=IF($Groups.G7="","",$Groups.G7)

The comma simply separates the arguments to the function. That formula simply says

If cell G7 in the sheet Groups is empty, display nothing; otherwise display the content of cell G7 in the sheet Groups

If this answer helped you, please accept it by clicking the check mark to the left and, karma permitting, upvote it. That will help other people with the same question.

In case you need clarification, edit your question (not an answer) or comment the relevant answer.

[anon73440385]

Hello,

start with LibreOffice Help - IF - which states that there are 3 arguments required.

1. Test - this tests a condition
2. What should happen, if the test is TRUE
3. What should happen if the test is not TRUE (FALSE)

and the , just separates these 3 item from each other. Now it should be easy to analyse each of your formulas:

Formula: IF($Groups.G7="","",$Groups.G7)

1. Test: Groups.G7="" means look in table Groups cell G7, if it is empty
2. TRUE: "" means: don’t show anything , if cell G7 in sheet Groups is empty
3. FALSE: Groups.G7 means: Show the value of cell G7 in sheet Groups (since there is one)

Formula: =IF(B5="","",IF(A5=3,IF(H4+18<=B5,"..",IF(H4<=B5,".","")),IF(6+18<=B5,"..",IF(6<=B5,".",""))))

This one is a quite complicated so-called nested IF, which means:

1. TEST: B5="" - means to test whether cell B5 is empty string.
2. TRUE: "" - show an empty string if B5 is an empty string
3. FALSE (an now it get’s hard) makes another distinction:

3.1) TEST: A5=3 test wether cell A5 equals 3
3.2) TRUE: - again makes a differentiation of what to happen

3.2.1) TEST: H4+18<=B5 - test, if adding 18 to cell H4 yields a value less or equal to cell B5
3.2.2)TRUE: “…” means show 2 dots

3.2.3) FALSE: makes the next differentiation
3.2.3.1) TEST: H4<=B5- test if cell value in H4 less or equal cell value in B5
3.2.3.2) TRUE: show a dot character .
3.2.3.3) FALSE: show an empty string no content)

3.3) FALSE: makes the next differentiation of what to happen
3.3.1) TEST: 6+18<=B5test if cell B5 greater or equal to 24` (6+18)
3.3.2) TRUE: “…” means show 2 dots

3.3.3) FALSE: makes the next differentiation
3.3.3.1) TEST: Cell content of B5 greater equal 6
3.3.3.2) TRUE: show a dot character .
3.3.3.3) FALSE: show an empty string no content)

This is hard to understand and it is obvious that nested IF statements should be avoided.

[LindaPro]

Back in the day, in COBOL (old programming language), this was considered spaghetti code and never recommended. Thanks for breaking this down.

[anon73440385]

I’m a COBOL-aware-ager