Prev: can I disable the delete command for a file
Next: Linking option buttons and returning data across worksheets
From: AleVe on 1 Mar 2010 09:20
Hi all,

I would like to create a chart showing spefici numbers regarding a specific
count made on a column. To illustrate my need, please see hereunder:

Considering I have a table composed of 3 columns:
Column A : Device names
Column B : Test Scenarii numbers
Column C : Test Scripts numbers

Devices Test Scenarii # Test Scripts #
A 1 1
A 1 2
B 1 3
A 1 4
A 2 2
B 2 3
C 2 5
C 3 6
C 3 7
A 4 1
A 4 4

If I need a count of TS numbers per device, I get this:
A -> Test Scenarii number = 6 as per the number of test scripts.
The fact is that I would like to get the number of unique Test scenarii
which should give me : A -> Test scenarii = 3 (test scsenarii numbers 1, 2
and 4 are the only 3 test scenarii implicated with the device A).

So my question is : Is there any Excel function that could allow me to get
these results? Or do I need to build a custom function on my own?

Thanks in advance for your answers.

Alex
From: T. Valko on 1 Mar 2010 13:57
Try this array formula** :

=SUM(IF(FREQUENCY(IF(A2:A12="A",B2:B12),B2:B12),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"AleVe" <alex93200(a)hotmail.com> wrote in message
news:7EFD9442-2FFA-43D9-A1E3-0EE52D8F5636(a)microsoft.com...
> Hi all,
>
> I would like to create a chart showing spefici numbers regarding a
> specific
> count made on a column. To illustrate my need, please see hereunder:
>
> Considering I have a table composed of 3 columns:
> Column A : Device names
> Column B : Test Scenarii numbers
> Column C : Test Scripts numbers
>
> Devices Test Scenarii # Test Scripts #
> A 1 1
> A 1 2
> B 1 3
> A 1 4
> A 2 2
> B 2 3
> C 2 5
> C 3 6
> C 3 7
> A 4 1
> A 4 4
>
> If I need a count of TS numbers per device, I get this:
> A -> Test Scenarii number = 6 as per the number of test scripts.
> The fact is that I would like to get the number of unique Test scenarii
> which should give me : A -> Test scenarii = 3 (test scsenarii numbers 1, 2
> and 4 are the only 3 test scenarii implicated with the device A).
>
> So my question is : Is there any Excel function that could allow me to get
> these results? Or do I need to build a custom function on my own?
>
> Thanks in advance for your answers.
>
> Alex


From: AleVe on 2 Mar 2010 06:20
Hi,

Thanks for the anwser. I tried with the given table (in my previous post)
and it works correctly, but the strange thing is when I test with my existing
table, it only calculates "0".

I wonder if it could be due to a specific format of my cells, I will give
you more information about what my cells contain.

The devices are only words without numbers in it (format of cells is
"General")
The Test Scenarii # are also with format "General" but they're composed this
way: AA.BB_C1_432

Other point which could may be important (but I don't think as the formula
perform a frequency count), my devices are not listed in a specific order,
thy're present randomly at the begining or in the middle of the column
without any specific order.

Hope this could give you hints to understand my issue :)

Thanks again.

"T. Valko" wrote:

> Try this array formula** :
>
> =SUM(IF(FREQUENCY(IF(A2:A12="A",B2:B12),B2:B12),1))
>
> ** array formulas need to be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
> key then hit ENTER.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "AleVe" <alex93200(a)hotmail.com> wrote in message
> news:7EFD9442-2FFA-43D9-A1E3-0EE52D8F5636(a)microsoft.com...
> > Hi all,
> >
> > I would like to create a chart showing spefici numbers regarding a
> > specific
> > count made on a column. To illustrate my need, please see hereunder:
> >
> > Considering I have a table composed of 3 columns:
> > Column A : Device names
> > Column B : Test Scenarii numbers
> > Column C : Test Scripts numbers
> >
> > Devices Test Scenarii # Test Scripts #
> > A 1 1
> > A 1 2
> > B 1 3
> > A 1 4
> > A 2 2
> > B 2 3
> > C 2 5
> > C 3 6
> > C 3 7
> > A 4 1
> > A 4 4
> >
> > If I need a count of TS numbers per device, I get this:
> > A -> Test Scenarii number = 6 as per the number of test scripts.
> > The fact is that I would like to get the number of unique Test scenarii
> > which should give me : A -> Test scenarii = 3 (test scsenarii numbers 1, 2
> > and 4 are the only 3 test scenarii implicated with the device A).
> >
> > So my question is : Is there any Excel function that could allow me to get
> > these results? Or do I need to build a custom function on my own?
> >
> > Thanks in advance for your answers.
> >
> > Alex
>
>
> .
>
From: T. Valko on 2 Mar 2010 11:52
>The Test Scenarii # are also with format "General"
>but they're composed this way: AA.BB_C1_432

OK, that's why it didn't work on your REAL data. The formula I suggested
will only work on numbers while your REAL data is TEXT (alphanumeric).

This is why it's important to post REAL information when asking a question.
Don't use made up scenarios/data.

Try this version. Assumes no empty cells in the column B range.

Array entered** :

=SUM(IF(FREQUENCY(IF(A2:A12="A",MATCH(B2:B12,B2:B12,0)),ROW(B2:B12)-ROW(B2)+1),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"AleVe" <alex93200(a)hotmail.com> wrote in message
news:1976D401-8009-4ACB-BD65-049139AB6054(a)microsoft.com...
> Hi,
>
> Thanks for the anwser. I tried with the given table (in my previous post)
> and it works correctly, but the strange thing is when I test with my
> existing
> table, it only calculates "0".
>
> I wonder if it could be due to a specific format of my cells, I will give
> you more information about what my cells contain.
>
> The devices are only words without numbers in it (format of cells is
> "General")
> The Test Scenarii # are also with format "General" but they're composed
> this
> way: AA.BB_C1_432
>
> Other point which could may be important (but I don't think as the formula
> perform a frequency count), my devices are not listed in a specific order,
> thy're present randomly at the begining or in the middle of the column
> without any specific order.
>
> Hope this could give you hints to understand my issue :)
>
> Thanks again.
>
> "T. Valko" wrote:
>
>> Try this array formula** :
>>
>> =SUM(IF(FREQUENCY(IF(A2:A12="A",B2:B12),B2:B12),1))
>>
>> ** array formulas need to be entered using the key combination of
>> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
>> SHIFT
>> key then hit ENTER.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "AleVe" <alex93200(a)hotmail.com> wrote in message
>> news:7EFD9442-2FFA-43D9-A1E3-0EE52D8F5636(a)microsoft.com...
>> > Hi all,
>> >
>> > I would like to create a chart showing spefici numbers regarding a
>> > specific
>> > count made on a column. To illustrate my need, please see hereunder:
>> >
>> > Considering I have a table composed of 3 columns:
>> > Column A : Device names
>> > Column B : Test Scenarii numbers
>> > Column C : Test Scripts numbers
>> >
>> > Devices Test Scenarii # Test Scripts #
>> > A 1 1
>> > A 1 2
>> > B 1 3
>> > A 1 4
>> > A 2 2
>> > B 2 3
>> > C 2 5
>> > C 3 6
>> > C 3 7
>> > A 4 1
>> > A 4 4
>> >
>> > If I need a count of TS numbers per device, I get this:
>> > A -> Test Scenarii number = 6 as per the number of test scripts.
>> > The fact is that I would like to get the number of unique Test scenarii
>> > which should give me : A -> Test scenarii = 3 (test scsenarii numbers
>> > 1, 2
>> > and 4 are the only 3 test scenarii implicated with the device A).
>> >
>> > So my question is : Is there any Excel function that could allow me to
>> > get
>> > these results? Or do I need to build a custom function on my own?
>> >
>> > Thanks in advance for your answers.
>> >
>> > Alex
>>
>>
>> .
>>


From: AleVe on 3 Mar 2010 07:42
Hi,

Many thanks your formula is correctly working, I have the result I was
looking for.

Alex

"T. Valko" wrote:

> >The Test Scenarii # are also with format "General"
> >but they're composed this way: AA.BB_C1_432
>
> OK, that's why it didn't work on your REAL data. The formula I suggested
> will only work on numbers while your REAL data is TEXT (alphanumeric).
>
> This is why it's important to post REAL information when asking a question.
> Don't use made up scenarios/data.
>
> Try this version. Assumes no empty cells in the column B range.
>
> Array entered** :
>
> =SUM(IF(FREQUENCY(IF(A2:A12="A",MATCH(B2:B12,B2:B12,0)),ROW(B2:B12)-ROW(B2)+1),1))
>
> ** array formulas need to be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
> key then hit ENTER.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "AleVe" <alex93200(a)hotmail.com> wrote in message
> news:1976D401-8009-4ACB-BD65-049139AB6054(a)microsoft.com...
> > Hi,
> >
> > Thanks for the anwser. I tried with the given table (in my previous post)
> > and it works correctly, but the strange thing is when I test with my
> > existing
> > table, it only calculates "0".
> >
> > I wonder if it could be due to a specific format of my cells, I will give
> > you more information about what my cells contain.
> >
> > The devices are only words without numbers in it (format of cells is
> > "General")
> > The Test Scenarii # are also with format "General" but they're composed
> > this
> > way: AA.BB_C1_432
> >
> > Other point which could may be important (but I don't think as the formula
> > perform a frequency count), my devices are not listed in a specific order,
> > thy're present randomly at the begining or in the middle of the column
> > without any specific order.
> >
> > Hope this could give you hints to understand my issue :)
> >
> > Thanks again.
 |  Next  |  Last
Pages: 1 2
Prev: can I disable the delete command for a file
Next: Linking option buttons and returning data across worksheets