[Solved] Calculation based on text

Discuss the spreadsheet application

[Solved] Calculation based on text

Postby markavo » Fri Dec 14, 2018 1:02 pm

Hello all

Can you help out with a formula please

Lets say column N as text in it the text i am looking for is Delivered or Ordered.
If columnist N equals Delivered or ordered (none case sensitive) add the price from Column M together for all that say Delivered or ordered and display where this formula is.

    M N
    1,900€ Delivered
    £1,799 Delivered
    £1,999 Ordered
    £3,999 In progress (do not add the price only add all delivered prices and Ordered price)
Last edited by markavo on Fri Dec 14, 2018 6:43 pm, edited 2 times in total.
Windows 7
Open Office 3.1
markavo
 
Posts: 6
Joined: Fri Dec 14, 2018 12:52 pm

Re: Calculation based on text

Postby John_Ha » Fri Dec 14, 2018 1:29 pm

Welcome to the forum.

You will find much useful information in the User Guides, the Writer, Base and Calc Tutorials and the AOO Frequently Asked Questions. May I suggest you bookmark the pages.

Look in the Calc Guide for the IF statement. See Easy way to make highly nested conditionals
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6456
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Calculation based on text

Postby markavo » Fri Dec 14, 2018 1:33 pm

Thank you, i will have a good read.

Much appreciated.
Windows 7
Open Office 3.1
markavo
 
Posts: 6
Joined: Fri Dec 14, 2018 12:52 pm

Re: Calculation based on text

Postby robleyd » Fri Dec 14, 2018 1:35 pm

You want a sum of all M where N has Delivered or Ordered? Is N likely to have other text in the same cell as Delivered or Ordered? Is M guaranteed to have non-blank cells in the range you want to sum?

Best if you could supply a sample spreadsheet file showing the kind of data you want to work with, and perhaps an example of the result you are wanting to achieve.

[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire and post the link here. The link also contains information on how to anonymise your document if it contains confidential information.
Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2512
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Calculation based on text

Postby John_Ha » Fri Dec 14, 2018 1:44 pm

It is often extremely useful to break the calculation into several small steps and use "helper columns" to hold the steps. These columns can then be hidden or, better, placed to the far right out of view. Alternatively, now you have the formulae you need you can create a single formula in one cell and dispense with the "helper columns".

In your example a simple IF statement will produce a "helper column" of values to be added. Now add those values.
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6456
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Calculation based on text

Postby markavo » Fri Dec 14, 2018 2:02 pm

Thanks all greatly appreciate the guidance.

Please find my end goal odds sheet to explain.
Thank you. :D
Attachments
calc.ods
(9.84 KiB) Downloaded 11 times
Windows 7
Open Office 3.1
markavo
 
Posts: 6
Joined: Fri Dec 14, 2018 12:52 pm

Re: Calculation based on text

Postby John_Ha » Fri Dec 14, 2018 4:22 pm

Look in Help for examples.

See Calc Functions listed by category for more help and examples. It says "These pages are a complete rewrite of the Function Help for Calc, with links to other relevant topics. The aim is to have more detail and support than the Help pages for other major spreadsheets. "

This is what I get when I click Help in the Function Wizard for IF. Learn how to use the Function Wizard.

Logical Functions
This category contains the Logical functions.

To access this command...
Insert - Function - Category Logical

AND
Returns TRUE if all arguments are TRUE. If one of the elements is FALSE, this function returns FALSE.
The arguments are either logical expressions themselves (TRUE, 1<5, 2+3=7, B8<10) that return logical values, or arrays (A1:C3) containing logical values.

When a function expects a single value, but you entered a cell range, then the value from the cell range is taken that is in the same column or row as the formula.

If the entered range is outside of the current column or row of the formula, the function returns the error value #VALUE!

Syntax
AND(LogicalValue1; LogicalValue2; ... LogicalValue30)
LogicalValue1; LogicalValue2; ... LogicalValue30 are 1 to 30 conditions to be checked. All conditions can be either TRUE or FALSE. If a range is entered as a parameter, the function uses the value from the range that is in the current column or row. The result is TRUE if the logical value in all cells within the cell range is TRUE.
Example
The logical values of entries 12<13; 14>12 and 7<6 are to be checked:
=AND(12<13;14>12;7<6) returns FALSE.
=AND(FALSE; TRUE) returns FALSE.

FALSE

Returns the logical value FALSE. The FALSE() function does not require any arguments and always returns the logical value FALSE.
Syntax
FALSE()
Example
=FALSE() returns FALSE
=NOT(FALSE()) returns TRUE

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.

NOT
Complements (inverts) a logical value.
Syntax
NOT(LogicalValue)
LogicalValue is any value to be complemented.
Example
=NOT(A). If A=TRUE then NOT(A) will return FALSE.

OR
Returns TRUE if at least one argument is TRUE. This function returns the value FALSE if all the arguments have the logical value FALSE.
The arguments are either logical expressions themselves (TRUE, 1<5, 2+3=7, B8<10) that return logical values, or arrays (A1:C3) containing logical values.

When a function expects a single value, but you entered a cell range, then the value from the cell range is taken that is in the same column or row as the formula.


If the entered range is outside of the current column or row of the formula, the function returns the error value #VALUE!

Syntax
OR(LogicalValue1; LogicalValue2; ... LogicalValue30)
LogicalValue1; LogicalValue2; ... LogicalValue30 are 1 to 30 conditions to be checked. All conditions can be either TRUE or FALSE. If a range is entered as a parameter, the function uses the value from the range that is in the current column or row.
Example
The logical values of entries 12<11; 13>22 and 45=45 are to be checked.
=OR(12<11;13>22;45=45) returns TRUE.
=OR(FALSE; TRUE) returns TRUE.

TRUE

The logical value is set to TRUE. The TRUE() function does not require any arguments and always returns the logical value TRUE.
Syntax
TRUE()
Example
If A=TRUE and B=FALSE the following examples appear:
=AND(A; B) returns FALSE
=OR(A; B) returns TRUE
=NOT(AND(A; B)) returns TRUE
Attachments
Clipboard03.png
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6456
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Calculation based on text

Postby John_Ha » Fri Dec 14, 2018 5:14 pm

See attached. Using "helper columns" makes it trivial. However the spreadsheet design is poor and is likely to lead to errors in use because you have the opportunity to insert conflicting data.

It is axiomatic that in any database design any item of data is inserted and stored once and once only. Thus you would be much better to have columns headed Quoted, No sale, Ordered, In progress, Delivered etc, and put a 1 in each column as the order situation changes.

I did the Euro conversion manually but it could presumably be done by an IF statement which checks the currency format of the cells in col A though I don't know how. Search the forum with currency for ideas.

I manually typed the " 1 " and " 0 " in cols D and E but they could easily be calculated by an IF statement together with an OR statement: IF (C4="In progress"OR"Delivered" ...) etc. However be aware that if you type "In progress" in col C you will inevitably misspell it sometimes, put in two spaces, add an extra space at the end, or type "In Progress", and these will not be recognised. It is far better to use codes such as P (Progress) D (Delivered) etc as they are visually correct. Or type the " 1 " in the respective column. The " 0 " in cols D and E is not required.

Or have a Status column where you place a D, P etc. You now cannot add conflicting data ... though you can add the wrong character and cause the calculation to be wrong.

The formula in F4 is as below

Code: Select all   Expand viewCollapse view
=IF((D4+E4>0);B4*(D4+E4);" ")

But will give the wrong result if you put a " 1 " in both col D and col E.

Clipboard01.png

The general advice for business users is "Never, repeat never, write business software. Always buy a package where all these Gotcha!s, and countless more, have been thought of and taken care of". For example, how are you handling the varying £-Euro rate? The same for all orders ignoring the changes? The value at order acceptance? The value at payment when you receive the Euros?

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.
Attachments
calc_jh.ods
(14.23 KiB) Downloaded 9 times
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6456
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Calculation based on text

Postby markavo » Fri Dec 14, 2018 6:41 pm

Thank you very much for your help.
Works great !
Windows 7
Open Office 3.1
markavo
 
Posts: 6
Joined: Fri Dec 14, 2018 12:52 pm

Re: Calculation based on text

Postby John_Ha » Fri Dec 14, 2018 8:07 pm

markavo wrote:Thank you very much for your help.
Works great !

Whatever else you do, do not use this for any business purpose. It will inevitably end in tears. You are asking basic beginner's questions which means you do not have the knowledge or skill to build a reliable business system.

Would you consent to surgery if the surgeon had never done any operation before and was getting medical advice on how to do your operation from strangers on an internet forum?
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6456
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 36 guests