Min Max Columns

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.
 
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.
Click to expand...

Note each left and right reading is in it's own cell
 
R

Ron Coderre

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)
 
H

Herbert Seidenberg

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
 
Top