I have a pivot table that reads from a large spreadsheet (A1:CI900). The
pivot table is used to display staffing costs and Effective Full Time
positions (EFT).

For one of my staff, their costs show but the EFT value does not. This value
is .23.

If I go to the data table and change the value to ANYTHING else, it will
then show on the pivot table. Even making it .231 works. Problem is, the .23
is calculated by a formula so I don't want to replace it with a value
(otherwise I could live with changing it by .001).

All details for all other staff appear correctly, except this one value.

Any suggestions would be really appreciated.

Hi,

Most likely the number is text in the source area. Choose that cell and
choose Edit, Clear All. Then reenter the value and refresh the pivot table.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"goldcoastdeb" wrote:

I have a pivot table that reads from a large spreadsheet (A1:CI900). The
pivot table is used to display staffing costs and Effective Full Time
positions (EFT).

For one of my staff, their costs show but the EFT value does not. This value
is .23.

If I go to the data table and change the value to ANYTHING else, it will
then show on the pivot table. Even making it .231 works. Problem is, the .23
is calculated by a formula so I don't want to replace it with a value
(otherwise I could live with changing it by .001).

All details for all other staff appear correctly, except this one value.

Any suggestions would be really appreciated.

Problem is, the .23 is calculated by a formula
so I don't want to replace it with a value

You can try adding a zero to the result from that formula, which would
coerce the text number to a real number w/o impacting on its numerical value.

Easiest for you to try this via a helper col. If the EFT calcs are in C2
down, then just place in D2: =C2+0, copy down all the way. Put a col label in
D1: EFT1 (say), then swap EFT for EFT1 in your pivot's layout.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

.. then swap EFT for EFT1 in your pivot's layout.

You probably need to extend the pivot's source range
to include the helper col D first before you can swap
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

Thanks, I thought that might work but it didn't.

"ShaneDevenshire" wrote:

Hi,

Most likely the number is text in the source area. Choose that cell and
choose Edit, Clear All. Then reenter the value and refresh the pivot table.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"goldcoastdeb" wrote:

I have a pivot table that reads from a large spreadsheet (A1:CI900). The
pivot table is used to display staffing costs and Effective Full Time
positions (EFT).

For one of my staff, their costs show but the EFT value does not. This value
is .23.

If I go to the data table and change the value to ANYTHING else, it will
then show on the pivot table. Even making it .231 works. Problem is, the .23
is calculated by a formula so I don't want to replace it with a value
(otherwise I could live with changing it by .001).

All details for all other staff appear correctly, except this one value.

Any suggestions would be really appreciated.

Thanks Max. I tried your suggestion but it did not fix the problem.

Deb

"Max" wrote:

Problem is, the .23 is calculated by a formula
so I don't want to replace it with a value

You can try adding a zero to the result from that formula, which would
coerce the text number to a real number w/o impacting on its numerical value.

Easiest for you to try this via a helper col. If the EFT calcs are in C2
down, then just place in D2: =C2+0, copy down all the way. Put a col label in
D1: EFT1 (say), then swap EFT for EFT1 in your pivot's layout.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

I have since discovered that if any staff have an EFT of .23 it is not
displaying on the pivot table.

I have also discovered (I did not establish this pivot table but have
inherited it), that the formula that produces the EFT value is rounded, ie
the formula starts with =ROUND. It is rounded to two decimal places. If I
take the rounding off the formula, the result then appears in the pivot
table! But it appears with about 10 decimal places. Obviously that works as a
'workaround' because I can format the pivot table each time to only show two
decimal places. Problem is, this file is used by my 7 team members, and my
boss relies on it heavily and i don't really want her to have to reformat
each time she wants to view the refreshed pivot table.

Any other suggestions?

Maybe you can post the EFT col's actual formula in the top cell?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---