Rounding to the nearest quarter (decimal) in a Pivot table

[Fred]

I tried posting this query a couple of weeks ago but my network crashed
and I have been unable to find the posting, so apologies if this is a
duplicate.

Each month I produce a pivot report of hours/days of work scheduled. I
would like to round the data part to the nearest quarter of an hour/day
(x.00, x.25, x.5 or x.75), I have seen and understand the principal of
MRound (and have the Analysis VBA toolpack addin) but can find no way
to apply this to a pivot table. I saw one posting that suggested
formatting the data cells but can find no way of specifying that I want
the above formatting applied.

Is there a way of achieving this kind of formatting to a Pivot report ?

Regards
Fred

[Fred]

OK, an update.

I tried putting conditional formatting in the Data portion of the Pivot
and received the usual warning about formatting being lost when the
data was refreshed. I tried using the Formula option,
=MRound(cell,0.25) and received the error message to the effect that I
cannot use references to other worksheets or workbooks in Conditional
Formatting, so changed to using Round instead,
=Round(cell/0.25,0)*0.25. The formula was accepted but the result in
the Pivot stayed exactly the same, apparently ignoring the formula. I
checked by putting the formula in a cell outside the Pivot, but
referring to the data portion of the Pivot and that worked fine. I
then tried copying and pasting only the Format and Values of the Pivot
report and again tried Conditional Formatting the data portion. This
time there was no error message about formatting being lost, because
it's no longer a Pivot, but the MRound/Round formulae still refuse to
change the values in the cells, i.e. 5.01 and 9.99 remain instead or
rounding to 5 and 10 respectively.

Can anyone offer any suggestions as to why this is/is not working as
expected please ?

Regards
Fred

[Annie1904]

I don't know the answer Fred, but found your post when I was looking for a
way to round to nearest 0.25 hours, and your comments gave me what I needed.
So, thanks.

"Fred" wrote:

> OK, an update.
>
> I tried putting conditional formatting in the Data portion of the Pivot
> and received the usual warning about formatting being lost when the
> data was refreshed. I tried using the Formula option,
> =MRound(cell,0.25) and received the error message to the effect that I
> cannot use references to other worksheets or workbooks in Conditional
> Formatting, so changed to using Round instead,
> =Round(cell/0.25,0)*0.25. The formula was accepted but the result in
> the Pivot stayed exactly the same, apparently ignoring the formula. I
> checked by putting the formula in a cell outside the Pivot, but
> referring to the data portion of the Pivot and that worked fine. I
> then tried copying and pasting only the Format and Values of the Pivot
> report and again tried Conditional Formatting the data portion. This
> time there was no error message about formatting being lost, because
> it's no longer a Pivot, but the MRound/Round formulae still refuse to
> change the values in the cells, i.e. 5.01 and 9.99 remain instead or
> rounding to 5 and 10 respectively.
>
> Can anyone offer any suggestions as to why this is/is not working as
> expected please ?
>
> Regards
> Fred
>
>

[mvl]

Fred,

Conditional formatting is applied to the cells, and doesn't move with
the pivot field if you change the layout.

I suggest that you copy and paste the pivot into a new worksheet as a value.
Then create a column for the rounding formula, and you will also be able to
create the conditionaly formatting too.

Good Luck,
Misty
See Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


"Fred" wrote:

> OK, an update.
>
> I tried putting conditional formatting in the Data portion of the Pivot
> and received the usual warning about formatting being lost when the
> data was refreshed. I tried using the Formula option,
> =MRound(cell,0.25) and received the error message to the effect that I
> cannot use references to other worksheets or workbooks in Conditional
> Formatting, so changed to using Round instead,
> =Round(cell/0.25,0)*0.25. The formula was accepted but the result in
> the Pivot stayed exactly the same, apparently ignoring the formula. I
> checked by putting the formula in a cell outside the Pivot, but
> referring to the data portion of the Pivot and that worked fine. I
> then tried copying and pasting only the Format and Values of the Pivot
> report and again tried Conditional Formatting the data portion. This
> time there was no error message about formatting being lost, because
> it's no longer a Pivot, but the MRound/Round formulae still refuse to
> change the values in the cells, i.e. 5.01 and 9.99 remain instead or
> rounding to 5 and 10 respectively.
>
> Can anyone offer any suggestions as to why this is/is not working as
> expected please ?
>
> Regards
> Fred
>
>