|
I'm trying to calculate the Mean Profile Depth (MPD) of a chip seal surface texture. The data is given in one column (blue) and I need to calculate the MPD in another column (pink), as shown: The original equation is: MPD = [(Max of 1st Half + Max of 2nd Half)/2]-Average of Total This needs to be a running average, but obviously the size and cell location of the first half and second half of data will change as I move down the column. So far, the only thing that is correct in my Excel equation is the last part, "AVERAGE(D$3:D12)". I need the other portion of the equation to look like "=(MAX($D$3:D7)+MAX(D$8:D12))/2", but this equation would change for each row: =(MAX($D$3:D7)+MAX(D$8:D12))/2 - AVERAGE(D$3:D12) =(MAX($D$3:D8)+MAX(D$8:D13))/2 - AVERAGE(D$3:D13) =(MAX($D$3:D8)+MAX(D$9:D14))/2 - AVERAGE(D$3:D14) =(MAX($D$3:D9)+MAX(D$9:D15))/2 - AVERAGE(D$3:D15) =(MAX($D$3:D9)+MAX(D$10:D16))/2 - AVERAGE(D$3:D16) =(MAX($D$3:D10)+MAX(D$10:D17))/2 - AVERAGE(D$3:D17) =... Obviously there is a pattern here, but I don't know if there is a way to make Excel see this pattern. Is there a way to code this equation so I don't have to go in and manually change the cells that MAX calls, for each row? And I know you guys probably understand this way better than I can, but here's a screenshot of what the equation should look like: 
Schleep fucked around with this message at 16:16 on Dec 15, 2009 |
#
¿
Dec 15, 2009 16:13
|
|
|
|
| # ¿ May 7, 2024 00:35 |
|
|
I couldn't figure out a way to get my other question answered so I've decided to put each parameter in a separate column. All I need it to do now is recognize this pattern in one column: ROW 5=MAX($D$3:D4) ROW 6=MAX($D$3:D4) ROW 7=MAX($D$3:D5) ROW 8=MAX($D$3:D5) ROW 9=MAX($D$3:D6) ROW 10=MAX($D$3:D6) ROW 11=MAX($D$3:D7) ROW 12=MAX($D$3:D7) =...ect and this pattern in the other column: ROW 5=MAX(D$5:D7) ROW 6=MAX(D$6:D8) ROW 7=MAX(D$6:D9) ROW 8=MAX(D$7:D10) ROW 9=MAX(D$7:D11) ROW 10=MAX(D$8:D12) ROW 11=MAX(D$8:D13) ROW 12=MAX(D$9:D14) =...ect When I try to fill the column, Excel won't recognize the pattern. Is there a way to do this without having to change each equation by hand? Edit: The "ROW #" isn't part of the equation, it's just to clarify what row each equation is on. Sorry for the confusion. Schleep fucked around with this message at 00:21 on Jan 4, 2010 |
|
#
¿
Dec 21, 2009 17:18
|
|
|
Bump? Is this impossible to do in Excel?
|
|
#
¿
Jan 4, 2010 00:22
|
|
|
This is perfect. The INDIRECT command is what I've been looking for and I'm so thankful for your help 
|
|
#
¿
Jan 4, 2010 16:20
|
|