reading E25 when I'm in 27->52 rows then E54 when I'm in 56->81 rows

[solquest]

Hello.
Nice to meet everyone, I'm brand new here.

I need some help, cause I need to create a formula that depends on some (A, B, C, D, E) cells in row 25 if I'm on rows between 27 and 52, while it depends on some (A, B, C, D, E) cells in row 54 if I'm on rows between 56 and 81 and so on every 29 rows.

If I had to stop at 52 row, I would have done something like this on row 27 and copied to all the rows until 52:

Please Login or Register  to view this content.

but I can't drag this to rows 27->56, cause when I'm in row 27->52 it needs to read always E25 (and if I use $E$25 it will read that cell also when in row 56).

How can I do it?

Thanks!

[ChemistB]

Before we get to your question, your formula can be greatly simplified. I'm still in the process of trying to interpret it but for example, the last bit

Please Login or Register  to view this content.

can be simplified to

Please Login or Register  to view this content.

Do you want your formula simplified?

[ChemistB]

Also, does anything else change besides the references to column E? Do we always refer to the ranges O3:Z23 for example?

[solquest]

Hey there.
Thanks for replying.
I actually ended up copying all the cells in row 25, pasting them in row 54 and then dragging down from 27 to 52 and from 56 to 81...and so on for the other 20 times I needed it.

I'm pretty sure those formulas can be simplified...I don't use Excel that much, so I started making one formula and then copying and pasting for adding more conditions...
So what would have been the original formula had to be split into thousands cells for avoiding excel formulas charachters' number.

I'm adding a small part of the file, so you can see what I mean (but as I said, I've done what I was looking for, so probably there's not need to spend too much time on it):

the goal is to detect the winning for different bets based on different results

this is the meaning of the file:
- cells A1->A6: 5 football matched
- cells B2->M6: the betting odds for different events (cells B1->M1) for each of those matches
- cells O2->Z23: the results that meet those events
- cells A25->E25 + A54->E54 + A83->E83 + A112->E112: the results of those 5 matches
- cells A27->C52 + A56->C81 + A85->C110 + A114->C139: the different betting combinations
- cells E27->E52 + E56->E81 + E85->E110 + E114->E139: if using only the first 4 matches or all of them
- cells F27->F52 + F56->F81 + F85->F110 + F114->F139: how much I would win if the results are the ones in cells A25->E25 + A54->E54 + A83->E83 + A112->E112 and the odds are the ones in the cells B2->M6
- cells G27->G52 + G56->G81 + G85->G110 + G114->G139: how much I'd pay for that bet combination
- cells H27->H52 + H56->H81 + H85->H110 + H114->H139: the net winning
- cells O27->NV52 + O56->NV81 + O85->NV110 + O114->NV139: the formulas for calculating the winning.

So for example, if results are 0-0 , 0-0, 1-0, 1-0, 1-0 in the combination X+goalpari (same amount of goals), the first two matches meet both the events, while the other three matches don't meet any.
This means that on 5 matches I'd win:
doubles: X1*X2+X1*goalpari2+goalpari1*X2+goalpari1*goalpari2
singles: X1+goalpari1+X2+goalpari2

(while if the results are 0-0, 0-0, 0-0, 0-0, 0-0 I would have to consider also quintuples, quadruplets and triplets)

I know that probably using VBA would have been better, but I've never used it and even if what I've done was pretty long, wasn't that difficult for me.

Cheers.