Can someone please help me to find a way round my problem. Here is a sample of


A B C D E
Product City Price Room night
H1 ROME 100.00 15
H2 LONDON 105.00 100
H3 ROME 203.00 5
H4 ROME 150.00 115
H5 LONDON 200.00 5

This is what I need.
On Column E I need the weighted average price for H1 based on room night.

Thanks.

=SUMPRODUCT(C2:C6,D2:D6)/SUM(D2:D6)
--
David Biddulph

Kim wrote:
Can someone please help me to find a way round my problem. Here is a
sample of


A B C D
E Product City Price Room night
H1 ROME 100.00 15
H2 LONDON 105.00 100
H3 ROME 203.00 5
H4 ROME 150.00 115
H5 LONDON 200.00 5

This is what I need.
On Column E I need the weighted average price for H1 based on room
night.

Thanks.

Hi David,

Thanks.

But I need to have the average based on the same city.

Regards,
Kim

"David Biddulph" wrote:

=SUMPRODUCT(C2:C6,D2:D6)/SUM(D2:D6)
--
David Biddulph

Kim wrote:
Can someone please help me to find a way round my problem. Here is a
sample of


A B C D
E Product City Price Room night
H1 ROME 100.00 15
H2 LONDON 105.00 100
H3 ROME 203.00 5
H4 ROME 150.00 115
H5 LONDON 200.00 5

This is what I need.
On Column E I need the weighted average price for H1 based on room
night.

Thanks.

Maybe...

=SUMPRODUCT(--(b2:b6="Rome"),C2:C6,D2:D6)/SUMif(b2:b6,"rome",D2:D6)

Kim wrote:

Hi David,

Thanks.

But I need to have the average based on the same city.

Regards,
Kim

"David Biddulph" wrote:

=SUMPRODUCT(C2:C6,D2:D6)/SUM(D2:D6)
--
David Biddulph

Kim wrote:
Can someone please help me to find a way round my problem. Here is a
sample of


A B C D
E Product City Price Room night
H1 ROME 100.00 15
H2 LONDON 105.00 100
H3 ROME 203.00 5
H4 ROME 150.00 115
H5 LONDON 200.00 5

This is what I need.
On Column E I need the weighted average price for H1 based on room
night.

Thanks.




--

Dave Peterson

=SUMPRODUCT(C$2:C$6,D$2:D$6,--(B$2:B$6=B2))/SUMIF(B$2:B$6,B2,D$2:D$6)
--
David Biddulph

Kim wrote:
Hi David,

Thanks.

But I need to have the average based on the same city.

Regards,
Kim

"David Biddulph" wrote:

=SUMPRODUCT(C2:C6,D2:D6)/SUM(D2:D6)
--
David Biddulph

Kim wrote:
Can someone please help me to find a way round my problem. Here is a
sample of


A B C D
E Product City Price Room night
H1 ROME 100.00 15
H2 LONDON 105.00 100
H3 ROME 203.00 5
H4 ROME 150.00 115
H5 LONDON 200.00 5

This is what I need.
On Column E I need the weighted average price for H1 based on room
night.

Thanks.

My sample data only have two cities but the actual data I have consist more
than 50 cities. So what's the best way ?

"David Biddulph" wrote:

=SUMPRODUCT(C$2:C$6,D$2:D$6,--(B$2:B$6=B2))/SUMIF(B$2:B$6,B2,D$2:D$6)
--
David Biddulph

Kim wrote:
Hi David,

Thanks.

But I need to have the average based on the same city.

Regards,
Kim

"David Biddulph" wrote:

=SUMPRODUCT(C2:C6,D2:D6)/SUM(D2:D6)
--
David Biddulph

Kim wrote:
Can someone please help me to find a way round my problem. Here is a
sample of


A B C D
E Product City Price Room night
H1 ROME 100.00 15
H2 LONDON 105.00 100
H3 ROME 203.00 5
H4 ROME 150.00 115
H5 LONDON 200.00 5

This is what I need.
On Column E I need the weighted average price for H1 based on room
night.

Thanks.

Would a PivotTable help?

Mohan

Kim wrote:
My sample data only have two cities but the actual data I have consist more
than 50 cities. So what's the best way ?

=SUMPRODUCT(C$2:C$6,D$2:D$6,--(B$2:B$6=B2))/SUMIF(B$2:B$6,B2,D$2:D$6)
--
[quoted text clipped - 29 lines]

Thanks.

--
Message posted via http://www.officekb.com