Pivot tables - using IF or COUNTIF in a Calculated field?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shubha.bala@gmail.com - 15 Jun 2007 20:27 GMT

Hi everyone,

I have created a pivot table which is looking good so far, however I
am having problems with this one additional column, which I think I
need a Calculated field for.

It looks like this:

Items A Items B
# Items Weighted # # Items Weighted
#
Red
Green
Blue
Purple

I also have a column in my source data called Priority. I want to
have a column beside Weighted # (for each of my Items) which shows me
the total number of rows in each colour group which has the priority
1. I don't care at all about the other priorities. I tried making a
calculated field that does this: =IF(Priority=1, 1, 0) but it isn't
working properly. It is showing me a value of 1 on some rows, but 0
on most of them.

I've tried it with other priority values and it still shows me
sporadic 1s. I also tried returning 1 for true and false, and it
showed a 1 in every column although I was expecting it to basically
show me a count of every single row with a value in Priority, since
I'm told calculated fields sum!

Lastly..I can't do this as a column item I think because it then ends
up showing me # Items and Weighted # based on priorities...but those
values I just want a total sum (priority doesn't matter).

I feel like I'm barking up the wrong tree with the IF idea. COUNTIF
gives me an error. Any thoughts?

Reply to this Message

Debra Dalgleish - 15 Jun 2007 21:15 GMT

Add a column to the source data, e.g. Priority1, and use a formula to
calculate if the priority value is 1:

=IF(E2=1,1,0)

Add the new column to the pivot table, as Sum of Priority1

> Hi everyone,
>
[quoted text clipped - 32 lines]
> I feel like I'm barking up the wrong tree with the IF idea. COUNTIF
> gives me an error. Any thoughts?

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

shubha.bala@gmail.com - 18 Jun 2007 16:32 GMT

> Add a column to the source data, e.g. Priority1, and use a formula to
> calculate if the priority value is 1:
[quoted text clipped - 46 lines]
>
> - Show quoted text -

That's what I was afraid of...so no way to do it without a new column?

Reply to this Message

shubha.bala@gmail.com - 18 Jun 2007 20:15 GMT

I'm also still curious as to *what* the IF in the calculated field is
doing...it's clearly doing something because 3 of the rows give me
some value of 1, and it's doing something weird because of how it
shows me a 1 in every row when I return 1 for true and false.

On Jun 18, 11:32 am, "shubha.b...@gmail.com" <shubha.b...@gmail.com>
wrote:

> > Add a column to the source data, e.g. Priority1, and use a formula to
> > calculate if the priority value is 1:
[quoted text clipped - 50 lines]
>
> - Show quoted text -

Reply to this Message

shubha.bala@gmail.com - 18 Jun 2007 20:53 GMT

On Jun 18, 3:15 pm, "shubha.b...@gmail.com" <shubha.b...@gmail.com>
wrote:

> I'm also still curious as to *what* the IF in the calculated field is
> doing...it's clearly doing something because 3 of the rows give me
[quoted text clipped - 60 lines]
>
> - Show quoted text -

Oh I figured it out...
I tried using a really cool equation which would return 1 if x = 1,
and 0 otherwise = round(sin(pi * 2^x-2)),0)
and through that eventually figured out that it is summing priority
FIRST, and THEN applying the equation. This is the most ridiculous
thing ever (for my purpose). But that explains the IF...it is only
returning 1 if the SUM of all priority equals 1, which is only true in
a couple of cases.

Is there any way around this summing in advance thing?

Reply to this Message

Rate this thread: