Better formulas

LordTim

New Member
Joined
May 29, 2019
Messages
13
Good morning all,
I am in the process of redesigning a spreadsheet that has ballooned into a monstrosity. With that said I wanted to get some input on a better formula to calculate data on 2 and most likely 3 data points. Right now the spreadsheet forces the users to paste their data into one tab, change their data source to their pivot table then refresh it to get the data they need. Then they copy the pivot table and paste it back as values which is then referenced with a +GETPIVOTDATA formula in each and every cell.

The problem was that when a data point did not show up for a month or a new one showed up in the formulas would fail resulting in the formulas being edited every month to line up with the data. Below is the same data with private information changed to something more generic. The data points would the account, the department and the date. I am considering setting all of these up on a different tab to allow for maintenance and using a SUMIFS along with named ranges to bring the formual totals to the main worksheet. What I would like to know is there a better way to write the formula?

Formula sample:
=+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52010000,"Department",AB29,"account description","Production Direct Labor")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52020000,"Department",AB29,"account description","Production Direct Labor Overtime")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52030000,"Department",AB29,"account description","Production Direct Contract Labor")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051010,"Department",AB29,"account description","Production Direct Labor Vacation")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051015,"Department",AB29,"account description","Production Direct Labor Sick Leave")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051021,"Department",AB29,"account description","Production Direct Labor Social Security/Medicare")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051022,"Department",AB29,"account description","Production Direct Labor FUI/SUI")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051040,"Department",AB29,"account description","Production Direct Labor Group Health")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051050,"Department",AB29,"account description","Production Direct Labor 401k Match")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",52051060,"Department",AB29,"account description","Production Direct Labor Workers Comp")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60010100,"Department",AB29,"account description","Production Indirect Labor")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60030000,"Department",AB29,"account description","Temporary Labor")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051010,"Department",AB29,"account description","Production Indirect Vacation")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051015,"Department",AB29,"account description","Production Indirect Sick Leave")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051021,"Department",AB29,"account description","Production Indirect Social Security/Medicare")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051030,"Department",AB29,"account description","Production Indirect Basic Life")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051040,"Department",AB29,"account description","Production Indirect Group Health")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051050,"Department",AB29,"account description","Production Indirect 401k Match")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051060,"Department",AB29,"account description","Production Indirect Workers Comp")+GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",60051070,"Department",AB29,"account description","Production Indirect Bereavement")

You will see this does not line up with the pivot formula but you get the idea of what they did to solve their problem but I am sure you would agree not a good way to set it up.


1591113274143.png
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,300
Office Version
  1. 365
Platform
  1. Windows
Wow... That formula is simply too long. Three options: a) in your source data, add a column with the grouping you need (and loop that through to a pivot table) or b) make a small table with e.g. the ledger account in column A and the ledger description in column B and have a simple =GETPIVOTDATA("Amount",'Pivot Atlas'!$A$3,"Ledger account",A2,"Department",AB29,"account description",B2) -> drag down and add a sum. Or c) bypass the pivot table and just work with SUMIFS formulas, see e.g. this tutorial (using an array variable): Excel formula: SUMIFS with multiple criteria and OR logic | Exceljet .
Hope that works,
Koen
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top