Craigc3814
Board Regular
- Joined
- Mar 7, 2016
- Messages
- 217
Using PowerPivot
My source data looks like the bold data below (note that the column headings change as the year goes on, only the most recent 11 invoices are brought into the data).
What I want to create is the numbered columns where number 1 looks for the max value between all 11 date columns and returns the max, then column 2 looks for the max between the last 10 (February-November) columns, column 3 looks for the max between the last 9 columns (March - November), all the way until the new column number 11 just returns the last date column.
Is this possible? using the max formula it tells me I can only look up two values, using Maxx from my understanding will always look through the entire row.
[TABLE="width: 1531"]
<colgroup><col><col><col span="9"><col span="2"><col span="9"><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1531"]
<colgroup><col><col><col span="9"><col span="2"><col span="9"><col span="2"></colgroup><tbody>[TR]
[TD]Where[/TD]
[TD]Type[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]3/1/2018[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]5/1/2018[/TD]
[TD="align: right"]6/1/2018[/TD]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]8/1/2018[/TD]
[TD="align: right"]9/1/2018[/TD]
[TD="align: right"]10/1/2018[/TD]
[TD="align: right"]11/1/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]Home[/TD]
[TD]BMD[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[/TR]
</tbody>[/TABLE]
My source data looks like the bold data below (note that the column headings change as the year goes on, only the most recent 11 invoices are brought into the data).
What I want to create is the numbered columns where number 1 looks for the max value between all 11 date columns and returns the max, then column 2 looks for the max between the last 10 (February-November) columns, column 3 looks for the max between the last 9 columns (March - November), all the way until the new column number 11 just returns the last date column.
Is this possible? using the max formula it tells me I can only look up two values, using Maxx from my understanding will always look through the entire row.
[TABLE="width: 1531"]
<colgroup><col><col><col span="9"><col span="2"><col span="9"><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1531"]
<colgroup><col><col><col span="9"><col span="2"><col span="9"><col span="2"></colgroup><tbody>[TR]
[TD]Where[/TD]
[TD]Type[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]3/1/2018[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]5/1/2018[/TD]
[TD="align: right"]6/1/2018[/TD]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]8/1/2018[/TD]
[TD="align: right"]9/1/2018[/TD]
[TD="align: right"]10/1/2018[/TD]
[TD="align: right"]11/1/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]Home[/TD]
[TD]BMD[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]
[/TR]
</tbody>[/TABLE]