Help with this formula

[spectraflame]

Could someone breakdown this formula for me? I did not create it but I need to better understand it and what portions are specific to specific cells.

=IF(OR(ISBLANK(M49),ISBLANK(C49)),"",IF(OR(ISBLANK(N49),Q49=0,Q49=-1),(L49-M49)*100/D49,((L49-M49)-(AVERAGE(OFFSET(O49,(Q49+1),0)/300*N49,OFFSET(O49,(Q49+(IF(OFFSET(A49,Q49,0)="SEED",0,1))),0)/300*N49)))*100/D49))

 

[DanteAmor]

=IF(OR(ISBLANK(M49),ISBLANK(C49))

If cell M49 is blank OR cell C49 is blank, then the result is blank

,""

,
If not

IF(OR(ISBLANK(N49),Q49=0,Q49=-1),

If cell N49 is blank OR cell O49 is zero OR cell Q49 is -1, then the result is this operation:

(L49-M49)*100/D49

,
If not
the result is this operation:
((L49-M49)-(AVERAGE(OFFSET(O49,(Q49+1),0)/300*N49,OFFSET(O49,(Q49+(IF(OFFSET(A49,Q49,0)="SEED",0,1))),0)/300*N49)))*100/D49))

 

[sadboy309]

Translate the recipe:

*Cs1: IF [M49] or [C49] is empty then <equal> is <equal>empty

*Cs2: IF not Cs1, so IF [N49] is empty or [Q49] <equal> (0 or -1) then <equal> <then equal="">(L49-M49)*100/D49

*Cs3: If not Cs2, so <equal> </equal>([L49]-[M49]) subtract for AVERAGE of 2 number (with number 1 is: OFFSET(O49,(Q49+1),0)/300*N49; and number 2 is: OFFSET(O49,(Q49+(IF(OFFSET(A49,Q49,0)="SEED",0,1))),0)/300*N49; temporarily translated: No.1: below [O49] row number [Q49] +1 division for 300 multi [N49], No.2: below [O49] row number [Q49] + if not find for "SEED" below [A49] row number [Q49] then all division for 300 multi [N49]). Then, all multi (*100/D49)

Hope you understand what I am writting.</then></equal></equal></equal></equal>

 

[spectraflame]

Thanks for the breakdown. The OFFSET appears to be referencing some hidden cells in the spreadsheet. Does the second portion of the IF NOT have a different outcome if A49 or Q49 equal SEED?

So it is 2 part formula that determines the average?

 

[sadboy309]

Correct, AVERAGE(OFFSET(...)/300*N7,OFFSET(...)/300*N7)

 

[spectraflame]

Am I getting closer? The AVERAGE takes the OFFSET and takes the value in O49 and uses the value in Q49+1 divided by 300 multiplied by N49. The second part then does almost the same thing but is looking for the value of SEED in the same row before the last part dividing by 300 etc. The SEED is still throwing me off but if the value of SEED is not found, 0 is used?

((L49-M49)-(AVERAGE(OFFSET(O49,(Q49+1),0)/300*N49,OFFSET(O49,(Q49+(IF(OFFSET(A49,Q49,0)="SEED",0,1))),0)/300*N49)))*100/D49))

 

[DanteAmor]

Am I getting closer? The AVERAGE takes the OFFSET and takes the value in O49 and uses the value in Q49+1 divided by 300 multiplied by N49. The second part then does almost the same thing but is looking for the value of SEED in the same row before the last part dividing by 300 etc. The SEED is still throwing me off but if the value of SEED is not found, 0 is used?

AVERAGE(OFFSET(O49,(Q49+1),0)/300*N49,OFFSET(O49,(Q49+(IF(OFFSET(A49,Q49,0)="SEED",0,1))),0)/300*N49

I explain to you with the following example:

Average of 2 cells:

Cell1
OFFSET(O49,(Q49+1),0)/300

And

Cell2
OFFSET(O49,(Q49+(IF(OFFSET(A49,Q49,0)="SEED",0,1))),0)/300*N49

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:19.96px;" /><col style="width:90.3px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >N</td><td >O</td><td >P</td><td >Q</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td >Average</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >48</td><td > </td><td > </td><td style="background-color:#ffff00; text-align:right; ">1.083</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >49</td><td > </td><td style="text-align:right; ">10</td><td > </td><td > </td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >50</td><td >SEED</td><td > </td><td style="text-align:right; ">25</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >51</td><td > </td><td > </td><td style="text-align:right; ">40</td><td > </td><td > </td></tr></table>

First cell, starts at O49 and moves down 2 rows, result of adding the value of Q49 + 1. Then the first cell has the value of 40.

Second cell, starts at O49 and moves 1 cell down, resulting from the value of Q49 = 1 + 0.
The result of 0 is because it revises the following: It starts in cell A49 and the number of rows in Q49 moves, in Q49 it is equal to 1, then it takes the value of A50 and compares it to "SEED" if they are equal , then returns a 0, if they are different then returns 1 .
In this case they are the same and return 0.
Then the Second cell has the value of 25.

Si N49 = 10 (following with the example)

Then
40 / 300 * 10 = 1.33
25 / 300 * 10 = 0.833

Average (1.33 and 0.833) = 1.083