Assign blank cells in a column with a value of 1 for sum purposes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I track machine problem issues and the time they take on a daily basis.
Usually there are multiples of the same issue in a day. To get around havng
to input the repeating issue and the times seperately, I just put the issue
down once with the total of the issue time and then in a third column the
number of occurences in the total if more than one.

Date: Issue: Minutes: # of Occurences
1/1/07 Xissue 15 3
1/1/07 Yissue 6
1/2/07 Xissue 4
1/2/07 Zissue 22 4

This is a simplified example as the real spreadsheet has additional columns
of data. Because of those other columns and their values in the rows, I
don't want to put a 1 in # of Occurences column when recording a single
instance issue (like Yissue and 1/2 Xissue above). But I want to run a
pivot giving me the sum of an issue's minutes and number of occurences.

Pivot Date
Issue Data 1/1/07-1/2/07
Xissue Sum of Minutes 19
Sum of Occurences 4
Yissue Sum of Minutes 6
Sum of Occurences 1
Zissue Sum of Minutes 22
Sum of Occurences 4

Is there a way to have the blank cells in the Occurences column have an
assigned value of 1? Or am I going to have to input 1.

Thank you
 
You could create a helper column which populates 1s in blank spaces and then
paste the values into the relevant column.

Example: assume Occurrences is in column 1. Enter the following in the
helper column (say, B1): =IF(ISBLANK(A1),1,A1) and fill down.

Then copy the column, choose paste special-values and paste the values in
the Occurrences column.

Then run your pivot table.

Dave
 
I track machine problem issues and the time they take on a daily basis.
Usually there are multiples of the same issue in a day. To get around havng
to input the repeating issue and the times seperately, I just put the issue
down once with the total of the issue time and then in a third column the
number of occurences in the total if more than one.

Date: Issue: Minutes: # of Occurences
1/1/07 Xissue 15 3
1/1/07 Yissue 6
1/2/07 Xissue 4
1/2/07 Zissue 22 4

This is a simplified example as the real spreadsheet has additional columns
of data. Because of those other columns and their values in the rows, I
don't want to put a 1 in # of Occurences column when recording a single
instance issue (like Yissue and 1/2 Xissue above). But I want to run a
pivot giving me the sum of an issue's minutes and number of occurences.

Pivot Date
Issue Data 1/1/07-1/2/07
Xissue Sum of Minutes 19
Sum of Occurences 4
Yissue Sum of Minutes 6
Sum of Occurences 1
Zissue Sum of Minutes 22
Sum of Occurences 4

Is there a way to have the blank cells in the Occurences column have an
assigned value of 1? Or am I going to have to input 1.

Thank you
Click to expand...

Since your doing a pivot on the data anyway, why don't you just put
each issue on a new row (perhaps can create a dropdown for the issue
type). Each row would have a "1" on it. All you would have to do is
pick the issue type from the drop down (or copy or type the issue) and
put the number of minutes. This will also enable you to see wide
fluctuations in minutes for the same type of issue..
Hope this helps,
George
 
hi andygoon

the (X,Y,Z)issue Sum of Minutes : looks like have nothing to do with the #
of occurence...
it may be simpler to just simply type #1 if there is 1 occurrence in an
issue..
Date: Issue: Minutes: # of Occurences
1/1/07 Xissue 15 3
Click to expand...

Does the sum of minutes <15> above are the SUM for the 3# of occurrences or
for each occurrence?

regards
 
Back
Top