Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding to the nearest quarter (decimal) in a Pivot table
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding to the nearest quarter (decimal) in a Pivot table
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding to the nearest quarter (decimal) in a Pivot table
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding to the nearest quarter (decimal) in a Pivot table
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
pivot table created from another pivot table | Excel Worksheet Functions | |||
how to delete/clean out the row list in pivot table | Excel Worksheet Functions | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Rounding number to the nearest quarter | Excel Worksheet Functions |