Prev: Sum if
Next: Formula help or suggestions??
From: SBecker on 17 May 2010 12:33 I have the following formula, =(SUMPRODUCT(--($E$2:$E$495>50000),--($E$2:$E$495<75001),$D$2:$D$495)/SUMPRODUCT(--($E$2:$E$495>50000),--($E$2:$E$495<75001))) Which is calculating averages of property values between specified ranges. I would like to be able to drill further into this data by grouping the averages by state. Suggestions? Thanks, Suzanne
From: Luke M on 17 May 2010 12:44 Why not add it as a criteria in your SUMPRODUCT? =(SUMPRODUCT(--($E$2:$E$495>50000),--($E$2:$E$495<75001),--(B2:B495="My State"),$D$2:$D$495)/SUMPRODUCT(--($E$2:$E$495>50000),--(B2:B495="My State"),--($E$2:$E$495<75001))) -- Best Regards, Luke M "SBecker" <SBecker(a)discussions.microsoft.com> wrote in message news:38C4E075-8BD4-4B58-8B81-57524C13AA8D(a)microsoft.com... >I have the following formula, > > =(SUMPRODUCT(--($E$2:$E$495>50000),--($E$2:$E$495<75001),$D$2:$D$495)/SUMPRODUCT(--($E$2:$E$495>50000),--($E$2:$E$495<75001))) > > Which is calculating averages of property values between specified ranges. > I would like to be able to drill further into this data by grouping the > averages by state. > > Suggestions? > > Thanks, > Suzanne
From: SBecker on 19 May 2010 09:56 Luke, Perfect!! Thank you so much.... Suzanne "Luke M" wrote: > Why not add it as a criteria in your SUMPRODUCT? > > =(SUMPRODUCT(--($E$2:$E$495>50000),--($E$2:$E$495<75001),--(B2:B495="My > State"),$D$2:$D$495)/SUMPRODUCT(--($E$2:$E$495>50000),--(B2:B495="My > State"),--($E$2:$E$495<75001))) > > -- > Best Regards, > > Luke M > "SBecker" <SBecker(a)discussions.microsoft.com> wrote in message > news:38C4E075-8BD4-4B58-8B81-57524C13AA8D(a)microsoft.com... > >I have the following formula, > > > > =(SUMPRODUCT(--($E$2:$E$495>50000),--($E$2:$E$495<75001),$D$2:$D$495)/SUMPRODUCT(--($E$2:$E$495>50000),--($E$2:$E$495<75001))) > > > > Which is calculating averages of property values between specified ranges. > > I would like to be able to drill further into this data by grouping the > > averages by state. > > > > Suggestions? > > > > Thanks, > > Suzanne > > > . >
|
Pages: 1 Prev: Sum if Next: Formula help or suggestions?? |