Prev: Excel 2007 dynamic chart range
Next: If conditions for Data Entry Form
From: Herbert Seidenberg on 13 Apr 2010 17:46
Excel 2007 PivotTable
Ditto Gary
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_13_10.xlsx

From: amitexcel on 13 Apr 2010 18:57
Thank you so much. Could you also post a brief description of how this works?

"Herbert Seidenberg" wrote:

> Excel 2007 PivotTable
> Ditto Gary
> http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_13_10.xlsx
>
> .
>
From: amitexcel on 13 Apr 2010 19:31
I am not sure what this formula is doing, and how this range [G] is defined.
IF(COUNTIFS([G],[T],[T],[G],[CHA],[CHA],[AC],[AC],[CHB],[CHB])*(LEFT([G])="T"),"G"&RIGHT([G]),[G])

"Herbert Seidenberg" wrote:

> Excel 2007 PivotTable
> Ditto Gary
> http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_13_10.xlsx
>
> .
>
From: Herbert Seidenberg on 14 Apr 2010 11:44
Excel 2007 Pivot Table
Added help page and notes.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_13_10.xlsx
Pdf preview:
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_13_10.pdf

From: amitexcel on 14 Apr 2010 17:19
I think that I got some of the mechanics of it.

What you created is a TABLE in Excel 2007.
And you used the data to create three more columns in the TABLE, namely GS,
TS, and AmtS.
What I did not get is the logic of creating GS and TS.
IF(COUNTIFS([G],[T],[T],[G],[CHA],[CHA],[AC],[AC],[CHB],[CHB])*(LEFT([G])="T"),"G"&RIGHT([G]),[G])

What is this part of the formula doing?
COUNTIFS([G],[T],[T],[G],[CHA],[CHA],[AC],[AC],[CHB],[CHB])

This is my interpretation of it.
Taking any particular row, if -
[G],[T] = the value in G is the same as in T
[T],[G] = the value in T is the same as in G
[CHA], [CHA] = the value in CHA is the same as in CHA
[AC], [AC] = the value in AC is the same as in AC
[CHB], [CHB] = the value in CHB is the same as in CHB

This interpretation cannot be right, because if the first statement is true,
then the second must also be true. And statements 3, 4, and 5 are
self-evidently true.

Lastly, this part of the formula yields a number – 0, 1, 2 …

The second part of this expression - *(LEFT([G])="T") – yields a yes or no.
What is their product? And how does that drive the rest of the formula?

I am just trying to understand the conceptual logic.

Thank you.




"Herbert Seidenberg" wrote:

> Excel 2007 Pivot Table
> Added help page and notes.
> http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_13_10.xlsx
> Pdf preview:
> http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_13_10.pdf
>
> .
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Excel 2007 dynamic chart range
Next: If conditions for Data Entry Form