SOLVED

# there must be a better way to do this than using multiple pivot tables

Copper Contributor

# there must be a better way to do this than using multiple pivot tables

I am trying to recreate a summary like the image below. I have a table of data that includes all the elements needed to produce this but at this point my skills limit me to using multiple pivot tables, hiding rows, using calculated fields for averages, etc. I imagine there is probably a better way. Can someone point me to it? Thank you

best response confirmed by Mich8261 (Copper Contributor)
Solution

# Re: there must be a better way to do this than using multiple pivot tables

=LET(rng,B2:C5,

REDUCE({"Air Rail Volume","Air Rail Tickets","Int' Air Rail Volume","Int' Air Rail Volume %","Int'l Air Rail Tickets","Int'l Air Rail Tickets %","Domestic Air Rail Volume","Domestic Air Rail Volume %","Domestic Air Rail Tickets","Domestic Air Rail Tickets %"},SEQUENCE(,COLUMNS(rng)),

LAMBDA(u,v,HSTACK(u,

VSTACK(INDEX(rng,1,v),

INDEX(rng,2,v),

INDEX(rng,3,v),

TEXT(INDEX(rng,3,v)/INDEX(rng,1,v),"00,00%"),

INDEX(rng,4,v),

TEXT(INDEX(rng,4,v)/INDEX(rng,2,v),"00,00%"),

INDEX(rng,1,v)-INDEX(rng,3,v),

TEXT(1-INDEX(rng,3,v)/INDEX(rng,1,v),"00,00%"),

INDEX(rng,2,v)-INDEX(rng,4,v),

TEXT(1-INDEX(rng,4,v)/INDEX(rng,2,v),"00,00%"))))))

With Office 365 or Excel for the web you can apply a dynamic array formula according to this example.

1 best response

Accepted Solutions
best response confirmed by Mich8261 (Copper Contributor)
Solution

# Re: there must be a better way to do this than using multiple pivot tables

=LET(rng,B2:C5,

REDUCE({"Air Rail Volume","Air Rail Tickets","Int' Air Rail Volume","Int' Air Rail Volume %","Int'l Air Rail Tickets","Int'l Air Rail Tickets %","Domestic Air Rail Volume","Domestic Air Rail Volume %","Domestic Air Rail Tickets","Domestic Air Rail Tickets %"},SEQUENCE(,COLUMNS(rng)),

LAMBDA(u,v,HSTACK(u,

VSTACK(INDEX(rng,1,v),

INDEX(rng,2,v),

INDEX(rng,3,v),

TEXT(INDEX(rng,3,v)/INDEX(rng,1,v),"00,00%"),

INDEX(rng,4,v),

TEXT(INDEX(rng,4,v)/INDEX(rng,2,v),"00,00%"),

INDEX(rng,1,v)-INDEX(rng,3,v),

TEXT(1-INDEX(rng,3,v)/INDEX(rng,1,v),"00,00%"),

INDEX(rng,2,v)-INDEX(rng,4,v),

TEXT(1-INDEX(rng,4,v)/INDEX(rng,2,v),"00,00%"))))))

With Office 365 or Excel for the web you can apply a dynamic array formula according to this example.