Help with large formula to take in to account many repeats

[excelxzzx]

Hi guys,

So with all of your help a couple of weeks ago, you managed to create two formulas for me. One of which would compare the latest Episode to an average (within a large data set), and another which would compare the latest episode to the previous episode.

The only problem I have with both of these formulas is that if there are more than two repeats present, they both seem to appear as #N/A. This is clear on the example provided. Is there a way that both of the formulas can work to take in to account if there are more than two repeats present?

I've attached the file and highlighted everything in yellow. It's incredibly hard to explain over text so it will make much more sense once it's open

Thanks so much for the help!

Sam

[excelxzzx]

And just to add, if all of the 'Rp.3', 'Rp.4' & 'Rp.5' lines are deleted, you'll notice that the formula works If they are kept in, it doesn't work for some reason

[Glenn Kennedy]

OMG!!!

What do you expect to see in M35, and why? What do you mean by "compare"?

[PaulM100]

In your current formula replace the MAX part with: =MAX(0+IF(LEFT($B$17:$B$32,3)="Ep.",RIGHT($B$17:$B$32,LEN($B$17:$B$32)-FIND(".",$B$17:$B$32))))
and the LARGE part with =LARGE(0+IF(LEFT($B$17:$B$32,3)="Ep.",RIGHT($B$17:$B$32,LEN($B$17:$B$32)-FIND(".",$B$17:$B$32))),2)

Array as before.

[excelxzzx]

Hi Glenn,

It's difficult to explain over email so I wrote it in the excel in yellow, but basically the formula starting in M35 should always compare the latest episode (EP.3, Cell B27 in this case) to the previous episode (EP.2, Cell B21 in this case). By compare, I mean a percentage change for the latest episode, vs the previous episode. Sorry if that wasn't clear!

Thanks @PaulM100 I'll give that a go now and let you know!

[excelxzzx]

Hi Paul,

Thank you so much! That did the trick perfectly. Much appreciated !!

Sam

[Glenn Kennedy]

Does this (short...) formula do what you want:

=INDEX(M:M,AGGREGATE(14,6,ROW($M$17:$M$32)/(ISNUMBER(SEARCH("Ep",$B$17:$B$32))),1))/INDEX(M:M,AGGREGATE(14,6,ROW($M$17:$M$32)/(ISNUMBER(SEARCH("Ep",$B$17:$B$32))),2))-1

and

=INDEX(M:M,AGGREGATE(14,6,ROW($M$17:$M$32)/(ISNUMBER(SEARCH("Ep",$B$17:$B$32))),1))/M15-1

If not what (manually calculated) do you expect to see???