[Solved] I think macro, not formula?

[Dongatrash]

I want to set up a spreadsheet as a small profit/loss report.
I have a recurring expense for interest.

I wish to have a cell (B15) do this: if B6= "CBA Interest" then (this cell, B15) = B10.
So if I type CBA Interest in B6, then, I want cell B15 to equal A10, which is a set amount.
And so on: if F6= "CBA Interest" then (this cell, F15) = A10.

Can I do this in Calc? As a Formula, or a Macro?

I somehow got a macro to add up coloured cells in Excel on my work computer before,(=ColourFunction(A2,B2:AG2,FALSE)/2) but reading the help for Calc has me wallowing.

If anyone can help, I'd appreciate it, thanks.

[robleyd]

A simple formula should do what you have asked here. I assume that in your first example B10 is a typo and should be A10; also that if the string CBA Interest is not present, the cell should display nothing. Then in B15:

Code: Select all

=IF(B6="CBA Interest";$A$10;"")

You can drag or copy that as needed; note that the reference to A10 is absolute - see [Tutorial] Absolute, relative and mixed references if you are not sure about the differences.

[Dongatrash]

Great, thanks David. Just what I wanted, but nowhere could I find how to write it. The explanation in the Function Wizard was no help.
And thanks for the link.
Cheers.

[robleyd]

I find the offline help - F1 - for AOO is reasonably detailed; for example the help for the IF function is thus:

IF
Specifies a logical test to be performed.
Syntax
IF(Test; ThenValue; OtherwiseValue)
Test is any value or expression that can be TRUE or FALSE.
ThenValue (optional) is the value that is returned if the logical test is TRUE.
OtherwiseValue (optional) is the value that is returned if the logical test is FALSE.
In the OpenOffice Calc functions, parameters marked as "optional" can be omitted only when no parameter follows. For example, in a function with four parameters, in which the last two parameters are marked as "optional", you can omit parameter 4 or parameters 3 and 4, but you cannot omit parameter 3 alone.
Examples
=IF(A1>5;100;"too small") If the value in A1 is higher than 5, the value 100 is entered in the current cell; otherwise, the text “too small” (without quotes) is entered.

[Dongatrash]

Thanks! Haven't thought to try that, always seem to be in a hurry. Cheers.

[robleyd]

It's oftentimes quicker than waiting for an answer here

[Dongatrash]

Back again. Can there be more than 1 "IF" formula per cell?
To explain what I want: If A5 = Rates, then A6 = CVC Shire Rates, or if A5 = Interest, then A6 = CBA, or if A5 = Bank charges, then A6 = CBA.
Thanks.

[robleyd]

Yes; but you might find VLOOKUP simpler than nesting IF statements.
[Tutorial] VLOOKUP questions & answers

[John_Ha]

See the Calc Manual for all available functions.

[Lupp]

Back again. Can there be more than 1 "IF" formula per cell?

If we go back in the sense of taking things literal, we should also clarify the terminology. The litaeral answer will be "No!", but you will see that's not what you meant. You will also find the reason for what experienced contributors to forums like this one tend to take questions not too literally.

By definition there is at most one formula in a cell.
Slightly simplified, the formula is made up of a leading "=" and a following expression.
The expression may be composed in well defined ways from constants, references, from operators associated with their operands, and of function names followed by their parameter lists (may be empty or may contain only one element) enclosed in parentheses.
The operands are expressions again where the apportionment may be implicit by rules of priority or explicit by pairs of parentheses.
Parameters also are expressions. As elements of a list they are always delimited by semicolons.

Examples:
=5+3+A1 (where A1 is a reference to the result of cell A1)
=5+3*2 (equivalent to =5+(3*2) due to the priority of * over +)
=TRUE()
=SIN(2.3)
=IF(A1<5;EXP(1);IF(A1>10;SQRT(A2);-1000.001))

The final example is a formula made of an expression based on the Calc function IF(). As any(*) application of IF() it has three parameters: The first is a condition which must evaluate to 'True' or 'False'. The second and the third parameters, the then-part and the else-part, are expressions again.

Being expressions these parameters can generally be formed in any way described above for expressions, and therefor can also contain nested expressions based on the IF() function.

Since nested calls to IF() tend to result in bad readability/maintainability, exprienced users tend to avoid them. They also tend to suggest to questioners more reliable and better maintainable alternatives. In many cases the means of choice is a lookup-table accessed with the help of VLOOKUP() e.g. or with a combination of MATCH() and INDEX() or in some similar way.
Avoiding the use of a lookup table, sometimes the CHOOSE() function offers some advantages over nested IF().

See also: https://ask.libreoffice.org/en/question ... e-in-calc/ (The SWIRCH() function mentioned there is not implemented in AOO, and not specified in 'OpenFormula'.)

You see: Insisting on a "simple answer to a simple question" you made me write a kind of tutorial. It's not what contributors can do every time again.

Footnote (*)
Unfortunately the specification permits to omit the third paramter or even both, the second and the third. Neber do so.

[Dongatrash]

Thank you one and all. I bumbled through the tutorial, with a bit of help from Google to remind me stuff you've already told me, and my SS is doing what I want.

Cheers to you all.

Folk like you helping folk like me, makes the world an easier place to be.