Re: SUMPRODUCT across 9 axes
From: Cornelius (Cornelius_at_discussions.microsoft.com)
Date: 02/09/05
- Next message: Henrik: "Problem with Array Formulas and ISNUMBER"
- Previous message: Chandni: "Re: Toggle multiple values in single cell"
- In reply to: Ken Wright: "Re: SUMPRODUCT across 9 axes"
- Next in thread: Ken Wright: "Re: SUMPRODUCT across 9 axes"
- Reply: Ken Wright: "Re: SUMPRODUCT across 9 axes"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 9 Feb 2005 15:53:03 -0800
Looks interesting. I'm not sure it will give me the detail I'm looking for,
but it is definitely worth trying, thanks Ken.
On another note, I suspect I can achieve some of the results I was looking
for by eliminating some of the axes (I'll just calculate the possible choices
on the data field first), and using OFFSET to redfine the ranges for time
period (i.e. IF("Quarterly",OFFSET(A1:Z1,x),IF("Monthly",OFFSET(A1:Z1,y)...
Thanks for all the help. If someone has an epiphany, I'd love to know the
solution.
ccc
"Ken Wright" wrote:
> Cornelius, from the sound of it you appear to be doing the kind of analysis
> that pivot tables were designed for. Have you looked at using those for
> your data at all?
>
> http://peltiertech.com/Excel/Pivots/pivotstart.htm
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> "Cornelius" <Cornelius@discussions.microsoft.com> wrote in message
> news:749CB3F7-2E7F-4FCC-B01A-D7262DE4E681@microsoft.com...
> >
> > Hi all. I've been developing a sheet that measures numerous aspects of my
> > business and allows any set of parameters to be displayed at a single
> time.
> > For instance, one could look at sales to New York on a weekly basis in
> 2004
> > through a particular channel or customer, then switch these parameters
> using
> > validated cells to Chicago quarterly 2004 all business.
> >
> > I've created all the sets of vlookups for a SUMPRODUCT to look at, each
> > corresponding to a large data grid that has the appropriate categories
> > (dollars or pounds, weeks, years, customer category, region, etc.). Here
> is
> > the formula:
> >
> >
> =SUMPRODUCT(($C22=Data!$M$5:$FN$5)*($O$82=Data!$J$7:$J$865)*($A22=Data!$E$7:
> $E$865)*($B22=Data!$F$7:$F$865)*($E$78:$Q$78=Data!$K$7:$K$865)*($N$116:$Z$11
> 6=Data!$M$4:$FN$4)*(NOT($D$132:$AD$132=Data!$B$7:$B$865))*(NOT($F$52:$H$52=D
> ata!$D$7:$D$865))*(NOT($C$88:$H$88=Data!$C$7:$C$865))*Data!$M$7:$FN$865)
> >
> > where Data!M7:FN865 is the data grid. The NOT functions eliminate certain
> > areas that shouldn't be calculated (i.e. looking at New York you ignore
> > Chicago, LA and Miami, but looking at all business you ignore nothing).
> >
> > This formula returns N/A. If I reduce the ranges to be used or ignored
> > (make $C$88:$H$88 just $C$88) I get a value. How do I overcome this? I
> have
> > used ranges like this in the past succesfully, but never so many.
>
>
>
- Next message: Henrik: "Problem with Array Formulas and ISNUMBER"
- Previous message: Chandni: "Re: Toggle multiple values in single cell"
- In reply to: Ken Wright: "Re: SUMPRODUCT across 9 axes"
- Next in thread: Ken Wright: "Re: SUMPRODUCT across 9 axes"
- Reply: Ken Wright: "Re: SUMPRODUCT across 9 axes"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
