Data Analysis without using Pivot Table

**amitverma76** · 11-01-2011, 07:42 AM

Hello Guys
I am having the problem in summarising the data (multidimensional). I have been asked to create a report by region, brand and feature according to month, quarter and year.
I am attaching the spreadsheet for the reference.
This report cannot be made by using Pivot Table/chart.
This has to be thru using formulas.
I am trying to use Vlookups but some how I cannot figure out how to consolidate or summarise Period(Months, Quarterly or yearly).
If anyone can please help me with this would be very helpful to me.
Appreciate of help in advance.
Thanks
AV

**Kyle123** · 11-01-2011, 08:09 AM

Why do you not want to use pivot tables? This is what they are designed for, even if you don't want to present pivot tables, I'd use them to get the data in a format that's useful

**amitverma76** · 11-01-2011, 08:12 AM

Thanks for your quick reply Kyle. but this is condition given that you cant use Pivot Tables. It has to done using formulas only.

**Kyle123** · 11-01-2011, 08:14 AM

hmm it said this on your brief:

4- Above exercise can be done with Pivot tables, but we prefer using formulas

**Kyle123** · 11-01-2011, 08:19 AM

Since this looks like homework, I'll point you in the right direction rather than telling you how to do it.

Start by sketching out what you want to show on your dashboard, this will give you the framework for which you can start building formulas. Having seen the data, and the fact that your project states that the dashboard should be dynamic. I'll point you towards "dynamic named ranges" and this link for dashboards

http://chandoo.org/wp/excel-dashboards/

It's got loads of great tips that will really help you

Also a pointer for your original question, having taken dynamic named ranges into account, have a look at sumproduct. In terms of summarising over a period, this should be fairly straightforward when you consider that a quarter is 3 columns

**royUK** · 11-01-2011, 08:53 AM

There's also the fact that you can create a Pivottable & use GetPivotData functions

**amitverma76** · 11-04-2011, 10:25 AM

Many Thanks Kyle. I really appreciate your kind help.
@Roy- thanks for your time n suggestion.

Data Analysis without using Pivot Table

LinkBack

Thread Tools

Rate This Thread

Display

Data Analysis without using Pivot Table

Re: Data Analysis without using Pivot Table

Re: Data Analysis without using Pivot Table

Re: Data Analysis without using Pivot Table

Re: Data Analysis without using Pivot Table

Re: Data Analysis without using Pivot Table

Re: Data Analysis without using Pivot Table

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions