(Was wrong question for problem though!) Multi-Criteria Multiplication

**maybelle** · 08-04-2009, 04:56 AM

Hi,

I'm hoping someone can help me with the following.

I have three reference cells (a1, a2, a3).

Below, starting from b4 to l4 I have years from 2005 to 2015, row 5 for the same range contains data, row 6 (c to l) contains a growth rate.

I need to come up with a formula that can multiply (x * y * z *... [product-like formula]) the growth rates for all the years where the current year (row 4) is bigger than reference the reference year in a1 or smaller than reference year in a2. A3 is then used to multiply that total.

Any help that you may be able to give is greatly appreciated.

Thanks!

**martindwilson** · 08-04-2009, 06:22 AM

please put on a worksheet and attach an example

**maybelle** · 08-04-2009, 07:06 AM

The file is now attached

Originally Posted by martindwilson

please put on a worksheet and attach an example

**martindwilson** · 08-04-2009, 07:34 AM

oh no it's not! if it's too big zip it first

**maybelle** · 08-04-2009, 07:37 AM

should be ok now...

**martindwilson** · 08-04-2009, 11:16 AM

the product of all those % is going to be very small eg
for say 2010 to 2014
=PRODUCT(J7:N7)
2.93461E-06 or 0.000002934613644136330

is that what you want ?

**maybelle** · 08-05-2009, 05:14 AM

Nope.

Actually there was a small mistake in my though process (should be product of growth rates so 1+..).

I reviewed the problem yesterday and realized that I couldn't see the forest for all the trees- I thought I would need a very complex solution when I can make due with my current dataset and just use a HLookup. I suppose that sometimes you can be too close to the problem!

Thanks anyway for your time,

M