Min Max Columns

  • Thread starter Thread starter dmosher
  • Start date Start date
D

dmosher

I have a pivot table that is laid out like this.

Month 1 2 3 4 5... 12
L R L R L R L R L R L R
1 _,_ 3,3 _,_ _,_ 2,2 _,_
2 3,3 _,_ _,_ _,_ 2,2 _,_
3 _,_ _,_ 3,3 _,_ _,_ 2,2
4 _,_ _,_ _,_ 3,3 3,3 _,_


For each month there are two readings a left and right. I need to find
the first left and subtract the last left in each row and
correspondingly on the right. The first and last reading can be in any
month. I also need to capture the numbers of months between readings
to calculate a wear rate per month. The _,_ indicates that no reading
was taken that month.
 
I have a pivot table that is laid out like this.

Month 1 2 3 4 5... 12
L R L R L R L R L R L R
1 _,_ 3,3 _,_ _,_ 2,2 _,_
2 3,3 _,_ _,_ _,_ 2,2 _,_
3 _,_ _,_ 3,3 _,_ _,_ 2,2
4 _,_ _,_ _,_ 3,3 3,3 _,_

For each month there are two readings a left and right. I need to find
the first left and subtract the last left in each row and
correspondingly on the right. The first and last reading can be in any
month. I also need to capture the numbers of months between readings
to calculate a wear rate per month. The _,_ indicates that no reading
was taken that month.
Click to expand...

Note each left and right reading is in it's own cell
 
Thanks for the re-post....that was a big help.

With your sample data in A1:M5
Month data begins in Col_B and ends in Col_M

Note: the below formulas assume that months with
no readings are blank, instead of containing _,_.

This formula (in sections for readability) returns
the FIRST LEFT VALUE less the LAST LEFT VALUE:
N2: =IF(COUNTA($B2:$M2)>1,INDEX(LEFT($B2:$M2,FIND
(",",$B2:$M2)-1),MATCH("*",$B2:$M2,0))-LOOKUP(REPT("Z",255),
$B2:$M2,LEFT($B2:$M2,FIND(",",$B2:$M2)-1)),"n/a")

and...this formula (also in sections for readability) returns
the FIRST RIGHT VALUE less the LAST RIGHT VALUE:
O2: =IF(COUNTA($B2:$M2)>1,INDEX(MID($B2:$M2,FIND
(",",$B2:$M2)+1,255),MATCH("*",$B2:$M2,0))-LOOKUP(REPT("Z",255),
$B2:$M2,MID($B2:$M2,FIND(",",$B2:$M2)+1,255)),"n/a")

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
If you want to do everything inside the Pivot Table,
add some calculated items to the PT.
Lets assume that part of your original data looks like this:
Month Hand Read MoRed
A 1 R 3 1
A 2 L 7 2
A 3 L
A 6 L
A 6 R 4 6
A 7 L
A 7 R
A 8 L 9 8
A 8 R
A 11 R
B 2 R 4 2
A thru E are the customers, Month is 1 thru 12.
MoRed is an additional column with the formula
=IF(ISNUMBER(Read),Month,"")
The PT generated from this data might look like this:

Month Hand
1 2 3 4 5 6 7 8
Acct Data L R L R L R L R L L R L R L R
A Read _ 3 7 _ _ _ _ _ _ _ 4 _ _ 9 _
MoRed _ 1 2 _ _ _ _ _ _ _ 6 _ _ 8 _
B Read _ _ _ 4 2 _ _ _ _ 8 _ _ _ _ _
MoRed _ _ _ 2 3 _ _ _ _ 6 _ _ _ _ _
C Read 6 _ _ _ _ _ _ 4 _ _ _ _ _ _ _
MoRed 1 _ _ _ _ _ _ 4 _ _ _ _ _ _ _
D Read _ 5 _ _ 3 _ _ _ _ _ _ _ _ _ 7
MoRed _ 1 _ _ 3 _ _ _ _ _ _ _ _ _ 8
E Read _ _ 4 _ _ _ _ _ _ _ _ _ 3 6 7
MoRed _ _ 2 _ _ _ _ _ _ _ _ _ 7 8 8

Click on one of the months in the PT and from the PT toolbar
Formulas > Calculated Items (not fields)
Create the formulas MaxMo SumMo and DiffMo:
=Max('1','2','3','4','5','6','7','8','9','10','11','12')
=Sum('1','2','3','4','5','6','7','8','9','10','11','12')
=2*MaxMo-SumMo
Hide MaxMo and SumMo. These are only helpers.
DiffMo will show the difference in the Read readings
for L and R respectively and in the next row
shows the month interval between the readings.
The second half of the PT will then look like this:

9 10 11 12 MaxMo SumMo DiffMo
L L R L R L R L R L R L R
_ _ _ _ _ _ _ 9 4 16 7 2 1
_ _ _ _ _ _ _ 8 6 10 7 6 5
_ _ 7 _ _ _ _ 8 7 10 11 6 3
_ _ 10 _ _ _ _ 6 10 9 12 3 8
7 _ _ _ 9 _ _ 7 9 13 13 1 5
9 _ _ _ 11 _ _ 9 11 10 15 8 7
_ 8 _ _ _ _ _ 8 7 11 12 5 2
_ 10 _ _ _ _ _ 10 8 13 9 7 7
_ _ _ _ _ _ _ 6 7 10 10 2 4
_ _ _ _ _ _ _ 8 8 10 15 6 1
 
Back
Top