Compounding returns with a lookup

[funkydim]

Hi All

I am struggling coming up with a easy way to compound returns from a large list.

Say i have a spreadsheet with
Column A (animals) Column B (return)
Dog 5
Dog 7
Cat 3
Cat 2
Mouse 9

I want to run the formula {=(product(1+B1:B4/100)-1)} which compounds the returns. But i only want to compound like animals.

So I would have a List sheet that contains the set of Animals so i could use a lookup function.

Column A (List)
Cat
Dog
Mouse

I thought something like =vlookup(A1, Animals(product(1+B1:B4/100)-1),2, false) but i need to get all the Cats and Dogs not just one!
Any help would be muchly appreciated.

Cheers

[funkydim]

PS writing out the product function =((1+A1)*(1+A2)*(1+A3))-1) but the number of variables is well variable so I think Product is the way to go here. Or maybe some sort of code?

[Fotis1991]

So the expected result is CAT=5 DOG=12 MOUSE=9 ??

If YES, then sumif does the job.

[funkydim]

Nope,

Dog 12.35%
Cat 5.06%
Mouse 9%

For Dog as a formula {=(product(1+ A2:A3/100)-1)} or =((1+5/100)*(1+7/100))-1)

So basically i want a 'productif' statement, its just that none seems to exist so you have to get creative.

PS you from Kythera? My Mum is from Karava and Dad Viaradika!

[Fotis1991]

...PS you from Kythera? My Mum is from Karava and Dad Viaradika!

No i am not from Kythera, but for my family and me, this island is our favourite place. We vist it since 1990, at least twice a year. Last time i was there in July! I will be there soon.

Unfortunnately, i have no idea about your goal. As you said sumif gives a 12% for dogs that is not correct. Sorry.

[funkydim]

Thanks for trying anyway.
ANyone else have an idea?
why doesn't excel have =PRODUCTIF?

[funkydim]

This should work in my opinion, but doesnt!
=IF(--(A2=A2:A5),(PRODUCT(1+B2:B5/100)-1)*100)

Using the unary -- to get 0/1 results so that it skips the invalid fields
When I evaluate the formula I get this: =IF(--{TRUE;TRUE;FALSE;FALSE;FALSE},--PRODUCT(1+B2:B5/100)-1)

So i would have hoped it would not calculate the FALSE ones, but still includes them therefore producting the entire field. Annoying!