[Solved] Formula required for calc

Discuss the spreadsheet application

[Solved] Formula required for calc

Postby drslim212 » Sat Dec 14, 2019 11:29 pm

I require a bespoke formula for my Calc spreadsheet.

After I export my orders csv file from our website, I open it with calc and add in 3 extra columns is CL, CM, CN
CM contains a formula which displays the total discount for this order. However, if there are 5 products in the order, this total discount displays in all the rows.

I therefore created column CN to display the total discount only once per order ID ie in the 1st row for the specific order as shown in image 2.
I need a formula for this please.

Thanks
Attachments
calc 2.JPG
as required
calc 1.JPG
as it is now
Last edited by MrProgrammer on Fri Dec 20, 2019 8:55 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Openoffice 4.1.0
Windows 7
drslim212
 
Posts: 9
Joined: Sat Dec 14, 2019 9:36 pm

Re: Formula required for calc

Postby RusselB » Sat Dec 14, 2019 11:57 pm

Welcome to the Forums.
Simply check if the order ID is the different from the one above it, and if it's different, do the calculation, otherwise return 0
The IF statement is well documented in the Help file.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6097
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Formula required for calc

Postby drslim212 » Sun Dec 15, 2019 1:08 pm

Hi, yes I have tried but cant get this right hence my request for support.
I need the formula to automatically check CL for the order ID.
Thanks
Openoffice 4.1.0
Windows 7
drslim212
 
Posts: 9
Joined: Sat Dec 14, 2019 9:36 pm

Re: Formula required for calc

Postby Villeroy » Sun Dec 15, 2019 2:09 pm

I stop answering requests for help with photographs of a document instead of a document. This makes absolutely no sense. Is the value the same or not? Is your formula or the whole approach correct or not? Nobody can tell without having the document.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28536
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formula required for calc

Postby drslim212 » Sun Dec 15, 2019 2:15 pm

No problem - uploaded the spreadsheet
Openoffice 4.1.0
Windows 7
drslim212
 
Posts: 9
Joined: Sat Dec 14, 2019 9:36 pm

Re: Formula required for calc

Postby drslim212 » Sun Dec 15, 2019 2:17 pm

Let me just check that file again - its wrong.
I will upload the correct one
Openoffice 4.1.0
Windows 7
drslim212
 
Posts: 9
Joined: Sat Dec 14, 2019 9:36 pm

Re: Formula required for calc

Postby drslim212 » Sun Dec 15, 2019 2:23 pm

I have uploaded then file as csv and ods

Thanks
Last edited by drslim212 on Wed Dec 18, 2019 2:08 pm, edited 1 time in total.
Openoffice 4.1.0
Windows 7
drslim212
 
Posts: 9
Joined: Sat Dec 14, 2019 9:36 pm

Re: Formula required for calc

Postby drslim212 » Wed Dec 18, 2019 1:20 pm

Hi,
Is anyone able to help?

Thanks
Openoffice 4.1.0
Windows 7
drslim212
 
Posts: 9
Joined: Sat Dec 14, 2019 9:36 pm

Re: Formula required for calc

Postby keme » Wed Dec 18, 2019 1:42 pm

See if the attached file helps.

Note that with typos from manually entered order IDs (typically leading/trailing spaces) or if other info is added to it (like ...-a/...-b down payment qualifiers), the suggested strategy will fail.

Also, inserting text will be good in some cases. In other cases a zero, and formatting to hide zero values, will be better. Not really an issue until you use the column for further calculations.
 Edit: Attached file deleted at owner's request 
Last edited by keme on Wed Dec 18, 2019 6:01 pm, edited 1 time in total.
User avatar
keme
Volunteer
 
Posts: 3365
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Formula required for calc

Postby drslim212 » Wed Dec 18, 2019 2:08 pm

Hi Keme,

Thanks you, works great.
I over complicated the formula when I tried to solve it.

Have a great day
Openoffice 4.1.0
Windows 7
drslim212
 
Posts: 9
Joined: Sat Dec 14, 2019 9:36 pm


Return to Calc

Who is online

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