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??