[Solved] I think macro, not formula?
-
- Posts: 25
- Joined: Mon Nov 05, 2012 4:52 am
[Solved] I think macro, not formula?
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.
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.
Last edited by robleyd on Tue Jun 05, 2018 11:24 am, edited 1 time in total.
Reason: Tagged [Solved] [robleyd, Moderator]
Reason: Tagged [Solved] [robleyd, Moderator]
OpenOffice 4.1.5 on Windows 10 Pro. Folk like you helping folk like me, makes the world an easier place to be.
Re: I think macro, not formula?
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:
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.
Code: Select all
=IF(B6="CBA Interest";$A$10;"")
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
-
- Posts: 25
- Joined: Mon Nov 05, 2012 4:52 am
Re: I think macro, not formula?
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.
And thanks for the link.
Cheers.
OpenOffice 4.1.5 on Windows 10 Pro. Folk like you helping folk like me, makes the world an easier place to be.
Re: I think macro, not formula?
I find the offline help - F1 - for AOO is reasonably detailed; for example the help for the IF function is thus:
Help wrote: 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.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
-
- Posts: 25
- Joined: Mon Nov 05, 2012 4:52 am
Re: [Solved] I think macro, not formula?
Thanks! Haven't thought to try that, always seem to be in a hurry. Cheers.
OpenOffice 4.1.5 on Windows 10 Pro. Folk like you helping folk like me, makes the world an easier place to be.
Re: [Solved] I think macro, not formula?
It's oftentimes quicker than waiting for an answer here
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
-
- Posts: 25
- Joined: Mon Nov 05, 2012 4:52 am
Re: [Solved] I think macro, not formula?
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.
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.
OpenOffice 4.1.5 on Windows 10 Pro. Folk like you helping folk like me, makes the world an easier place to be.
Re: [Solved] I think macro, not formula?
Yes; but you might find VLOOKUP simpler than nesting IF statements.
[Tutorial] VLOOKUP questions & answers
[Tutorial] VLOOKUP questions & answers
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: [Solved] I think macro, not formula?
See the Calc Manual for all available functions.
LO 6.4.4.2, Windows 10 Home 64 bit
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
Re: [Solved] I think macro, not formula?
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.Dongatrash wrote:Back again. Can there be more than 1 "IF" formula per cell?
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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
-
- Posts: 25
- Joined: Mon Nov 05, 2012 4:52 am
Re: [Solved] I think macro, not formula?
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.
Cheers to you all.
Folk like you helping folk like me, makes the world an easier place to be.
OpenOffice 4.1.5 on Windows 10 Pro. Folk like you helping folk like me, makes the world an easier place to be.