Pivot table as formula - possible - how

Discuss the spreadsheet application

Pivot table as formula - possible - how

Postby Albireo » Mon Jan 27, 2020 12:17 pm

Hope I can explain what I want :)
Suppose I have the following columns (below). (Column1 contains text and column2 contains numbers)

I want to sum all 1111, 111-111, 2222, 123 (and create a pivot table using a formula)
Is it possible with OO?

  • Col-A Name
1111
111-111
2222
2222
111-111
1111
123

  • Col-B Number
5
12
14
3
28
13
7


Columns C and D show the result as follows .:
1111 = 18
111-111 = 40
2222 = 17
123 = 7
OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm

Re: Pivot table as formula - possible - how

Postby Villeroy » Mon Jan 27, 2020 1:50 pm

A pivot table works without any formulas. https://wiki.openoffice.org/wiki/Docume ... /DataPilot
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: 28546
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pivot table as formula - possible - how

Postby Albireo » Mon Jan 27, 2020 2:52 pm

Yes - but!
Pivot tables are a powerful tool.
If the conditions are changed, it is possible to change the pivot table and layout.

However (when I was using pivot tables - log time ago) there were some limitations.
- No empty rows / columns in the table
- If the default values change, the pivot table is not updated.
(were the ones I spontaneously remember - there are certainly more limitations)
Would the table in the Calc be sorted?
Are these restrictions or any others remaining?

But I just want a calculation (no formatting) - similar to CALCULATEIF ...
Ie sum all values for each alternative, using a formula - Is it possible in OO?
OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm

Re: Pivot table as formula - possible - how

Postby Villeroy » Mon Jan 27, 2020 3:07 pm

Automatic update for pivots could turn out to be a nightmare because after every edit you would have to wait for the pivot to update. It's a whole generated table. If you put the pivot on a separate sheet, a simple macro can do the update when the sheet is activated so you never see an old pivot.
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: 28546
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pivot table as formula - possible - how

Postby Lupp » Mon Jan 27, 2020 3:14 pm

If you want parts of the functionality of pivot tables or enhancements to it you will have to do a lot of programming, imo. The tool itself will not work without a destination, and you need to provide the required space in a sheet for it.

If it is about refreshing a pivot table similar to cells containing formulas under AutoCalculate: A user function getting passed the source range data and info about the source range (location) itself can identify the pivot table(s) needing a refresh and trigger it. Efficiency of your sheets under AutoCalculate may significantly go down. (Have a lot of fun!)
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2914
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Pivot table as formula - possible - how

Postby Albireo » Mon Jan 27, 2020 5:31 pm

OK!

Thought there was some "easy" way / formula to accomplish this.
1.) Filter out unique content in a column
2.) Find and add the number in the next column

Is it possible to manually update the pivot table?
OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm

Re: Pivot table as formula - possible - how

Postby MrProgrammer » Mon Jan 27, 2020 6:06 pm

Albireo wrote:Thought there was some "easy" way / formula to accomplish this.
1.) Filter out unique content in a column
2.) Find and add the number in the next column
Calc can perform those operations with formulas. Whether they are "easy" depends on your skill level. If you don't understand the second tutorial, stick with pivot tables.

Don't even think about attempting this project until you are thoroughly familiar with this material:
[Tutorial] Ten concepts that every Calc user should know

#1 is accomplished by writing filter formulas and using "not equal to any previous entry" as the filter criteria. You can sort the filtered results if you like, though this requires more formulas. Be advised that you will want to use auxillary columns for these calculations.
[Tutorial] Sorting and Filtering data with formulas

#2 is accomplished with the SUMPRODUCT formula once the filtered values are obtained from #1:
[Tutorial] The SUMPRODUCT function

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3961
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Pivot table as formula - possible - how

Postby Villeroy » Wed Jan 29, 2020 12:40 pm

Albireo wrote:OK!

Thought there was some "easy" way / formula to accomplish this.

Why would pivot tables exist then?
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: 28546
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests