Please explain function/formula

I haven't had any luck with my previous query, so I did some trial and error,
and produced a formula that gives me quite a nice graph that shows what I
want. The problem is, I can't explain why.

My data is in columns B to O, and in the fourteen columns to the right of
that I have entered:
=(1-(COUNTIF(B6:$O6,0)+COUNTBLANK(B6:$O6))/(COUNTA(B6:$O6)+COUNTBLANK(B6:$O6)))*SUM($B6:$O6)

If I fill this across and down and produce a line graph of the results, it
shows very clearly who is producing well and who isn't, but I can't present
it without being able to tell what it does.

I hope someone can give me a word or definition for what's happening here -
if there's a simple function that will do the same or similar for me, even
better!

Let's break it apart, shall we:

=(1-(COUNTIF(B6:$O6,0)+COUNTBLANK(B6:$O6))/(COUNTA(B6:$O6)+COUNTBLANK(B6:$O6)))*SUM($B6:$O6)


COUNTIF(B6:$O6,0)
The COUNTIF function is counting the number of cells between B(row) and
O(row) that are equal to zero.

COUNTBLANK(B6:$O6)
The COUNTBLANK function is counting the number of cells that are blank.

COUNTA(B6:$O6)
Counts the number of non-blank cells.

SUM($B6:$O6) - totals the values entered in B(row)-O(row)

You should be able to put it together from here.
"Tara H" wrote:

> I haven't had any luck with my previous query, so I did some trial and error,
> and produced a formula that gives me quite a nice graph that shows what I
> want. The problem is, I can't explain why.
>
> My data is in columns B to O, and in the fourteen columns to the right of
> that I have entered:
> =(1-(COUNTIF(B6:$O6,0)+COUNTBLANK(B6:$O6))/(COUNTA(B6:$O6)+COUNTBLANK(B6:$O6)))*SUM($B6:$O6)
>
> If I fill this across and down and produce a line graph of the results, it
> shows very clearly who is producing well and who isn't, but I can't present
> it without being able to tell what it does.
>
> I hope someone can give me a word or definition for what's happening here -
> if there's a simple function that will do the same or similar for me, even
> better!

The problem is, I know what each of the bits do, I built it a bit at a time,
but I don't know how to explain it simply to busy people. The intention is
to show the records with the highest percentage of 0 or blank as low values,
and the lowest percentage of 0 or blank as high values, but also taking into
account the actual value of the production. So a high-value producer with a
couple of 0's looks better than a low-value producer with the same number of
0's. It seems like the kind of thing that should be a fairly simple
statistical function, but I don't know a lot about statistics, hence the
trouble.

Thanks,
Tara

"Barb Reinhardt" wrote:

> Let's break it apart, shall we:
>
> =(1-(COUNTIF(B6:$O6,0)+COUNTBLANK(B6:$O6))/(COUNTA(B6:$O6)+COUNTBLANK(B6:$O6)))*SUM($B6:$O6)
>
>
> COUNTIF(B6:$O6,0)
> The COUNTIF function is counting the number of cells between B(row) and
> O(row) that are equal to zero.
>
> COUNTBLANK(B6:$O6)
> The COUNTBLANK function is counting the number of cells that are blank.
>
> COUNTA(B6:$O6)
> Counts the number of non-blank cells.
>
> SUM($B6:$O6) - totals the values entered in B(row)-O(row)
>
> You should be able to put it together from here.
> "Tara H" wrote:
>
> > I haven't had any luck with my previous query, so I did some trial and error,
> > and produced a formula that gives me quite a nice graph that shows what I
> > want. The problem is, I can't explain why.
> >
> > My data is in columns B to O, and in the fourteen columns to the right of
> > that I have entered:
> > =(1-(COUNTIF(B6:$O6,0)+COUNTBLANK(B6:$O6))/(COUNTA(B6:$O6)+COUNTBLANK(B6:$O6)))*SUM($B6:$O6)
> >
> > If I fill this across and down and produce a line graph of the results, it
> > shows very clearly who is producing well and who isn't, but I can't present
> > it without being able to tell what it does.
> >
> > I hope someone can give me a word or definition for what's happening here -
> > if there's a simple function that will do the same or similar for me, even
> > better!

I never saw your original post on this perhaps you can explain your data a
bit more?

You have columns from B to O next to peoples names, there are numbers or
zeros in each of these columns. How do you distinguish a high-value
producers vs the low-value producers?

"Tara H" wrote:

> The problem is, I know what each of the bits do, I built it a bit at a time,
> but I don't know how to explain it simply to busy people. The intention is
> to show the records with the highest percentage of 0 or blank as low values,
> and the lowest percentage of 0 or blank as high values, but also taking into
> account the actual value of the production. So a high-value producer with a
> couple of 0's looks better than a low-value producer with the same number of
> 0's. It seems like the kind of thing that should be a fairly simple
> statistical function, but I don't know a lot about statistics, hence the
> trouble.
>
> Thanks,
> Tara
>
> "Barb Reinhardt" wrote:
>
> > Let's break it apart, shall we:
> >
> > =(1-(COUNTIF(B6:$O6,0)+COUNTBLANK(B6:$O6))/(COUNTA(B6:$O6)+COUNTBLANK(B6:$O6)))*SUM($B6:$O6)
> >
> >
> > COUNTIF(B6:$O6,0)
> > The COUNTIF function is counting the number of cells between B(row) and
> > O(row) that are equal to zero.
> >
> > COUNTBLANK(B6:$O6)
> > The COUNTBLANK function is counting the number of cells that are blank.
> >
> > COUNTA(B6:$O6)
> > Counts the number of non-blank cells.
> >
> > SUM($B6:$O6) - totals the values entered in B(row)-O(row)
> >
> > You should be able to put it together from here.
> > "Tara H" wrote:
> >
> > > I haven't had any luck with my previous query, so I did some trial and error,
> > > and produced a formula that gives me quite a nice graph that shows what I
> > > want. The problem is, I can't explain why.
> > >
> > > My data is in columns B to O, and in the fourteen columns to the right of
> > > that I have entered:
> > > =(1-(COUNTIF(B6:$O6,0)+COUNTBLANK(B6:$O6))/(COUNTA(B6:$O6)+COUNTBLANK(B6:$O6)))*SUM($B6:$O6)
> > >
> > > If I fill this across and down and produce a line graph of the results, it
> > > shows very clearly who is producing well and who isn't, but I can't present
> > > it without being able to tell what it does.
> > >
> > > I hope someone can give me a word or definition for what's happening here -
> > > if there's a simple function that will do the same or similar for me, even
> > > better!

Hi Tim,

My original query is at:
http://www.microsoft.com/office/comm...8-3543ee85a4d4.

While trying to figure that out, the formula developed into something rather
more complex, but much more useful-looking. When I graph the results, it is
immediately clear which are the high and low producers, and I can verify this
by looking carefully at the figures in the table, I'm just not really sure
how it does this. From examining it it seems to give the people who have
been consistently producing good figures more credit when a 0 occurs than the
people with low figures or many zeroes, so smoothing out the graph which is
otherwise very up-and-down over the course of the 14 weeks.

What worries me is that the people I'm making the graph for, while not that
hot on Excel, are high-level financial people, so I need a better explanation
for them than, 'well it seems to work' !

Thanks,
Tara

"tim m" wrote:

> I never saw your original post on this perhaps you can explain your data a
> bit more?
>
> You have columns from B to O next to peoples names, there are numbers or
> zeros in each of these columns. How do you distinguish a high-value
> producers vs the low-value producers?
>
> "Tara H" wrote:
>
> > The problem is, I know what each of the bits do, I built it a bit at a time,
> > but I don't know how to explain it simply to busy people. The intention is
> > to show the records with the highest percentage of 0 or blank as low values,
> > and the lowest percentage of 0 or blank as high values, but also taking into
> > account the actual value of the production. So a high-value producer with a
> > couple of 0's looks better than a low-value producer with the same number of
> > 0's. It seems like the kind of thing that should be a fairly simple
> > statistical function, but I don't know a lot about statistics, hence the
> > trouble.
> >
> > Thanks,
> > Tara
> >
> > "Barb Reinhardt" wrote:
> >
> > > Let's break it apart, shall we:
> > >
> > > =(1-(COUNTIF(B6:$O6,0)+COUNTBLANK(B6:$O6))/(COUNTA(B6:$O6)+COUNTBLANK(B6:$O6)))*SUM($B6:$O6)
> > >
> > >
> > > COUNTIF(B6:$O6,0)
> > > The COUNTIF function is counting the number of cells between B(row) and
> > > O(row) that are equal to zero.
> > >
> > > COUNTBLANK(B6:$O6)
> > > The COUNTBLANK function is counting the number of cells that are blank.
> > >
> > > COUNTA(B6:$O6)
> > > Counts the number of non-blank cells.
> > >
> > > SUM($B6:$O6) - totals the values entered in B(row)-O(row)
> > >
> > > You should be able to put it together from here.
> > > "Tara H" wrote:
> > >
> > > > I haven't had any luck with my previous query, so I did some trial and error,
> > > > and produced a formula that gives me quite a nice graph that shows what I
> > > > want. The problem is, I can't explain why.
> > > >
> > > > My data is in columns B to O, and in the fourteen columns to the right of
> > > > that I have entered:
> > > > =(1-(COUNTIF(B6:$O6,0)+COUNTBLANK(B6:$O6))/(COUNTA(B6:$O6)+COUNTBLANK(B6:$O6)))*SUM($B6:$O6)
> > > >
> > > > If I fill this across and down and produce a line graph of the results, it
> > > > shows very clearly who is producing well and who isn't, but I can't present
> > > > it without being able to tell what it does.
> > > >
> > > > I hope someone can give me a word or definition for what's happening here -
> > > > if there's a simple function that will do the same or similar for me, even
> > > > better!

I'm still a little foggy on how you are defining high or low producers. Is
it strictly defined as the higher the numbers in the column the higher the
producer or is there something different.

"Tara H" wrote:

> Hi Tim,
>
> My original query is at:
> http://www.microsoft.com/office/comm...8-3543ee85a4d4.
>
> While trying to figure that out, the formula developed into something rather
> more complex, but much more useful-looking. When I graph the results, it is
> immediately clear which are the high and low producers, and I can verify this
> by looking carefully at the figures in the table, I'm just not really sure
> how it does this. From examining it it seems to give the people who have
> been consistently producing good figures more credit when a 0 occurs than the
> people with low figures or many zeroes, so smoothing out the graph which is
> otherwise very up-and-down over the course of the 14 weeks.
>
> What worries me is that the people I'm making the graph for, while not that
> hot on Excel, are high-level financial people, so I need a better explanation
> for them than, 'well it seems to work' !
>
> Thanks,
> Tara
>
> "tim m" wrote:
>
> > I never saw your original post on this perhaps you can explain your data a
> > bit more?
> >
> > You have columns from B to O next to peoples names, there are numbers or
> > zeros in each of these columns. How do you distinguish a high-value
> > producers vs the low-value producers?
> >
> > "Tara H" wrote:
> >
> > > The problem is, I know what each of the bits do, I built it a bit at a time,
> > > but I don't know how to explain it simply to busy people. The intention is
> > > to show the records with the highest percentage of 0 or blank as low values,
> > > and the lowest percentage of 0 or blank as high values, but also taking into
> > > account the actual value of the production. So a high-value producer with a
> > > couple of 0's looks better than a low-value producer with the same number of
> > > 0's. It seems like the kind of thing that should be a fairly simple
> > > statistical function, but I don't know a lot about statistics, hence the
> > > trouble.
> > >
> > > Thanks,
> > > Tara
> > >
> > > "Barb Reinhardt" wrote:
> > >
> > > > Let's break it apart, shall we:
> > > >
> > > > =(1-(COUNTIF(B6:$O6,0)+COUNTBLANK(B6:$O6))/(COUNTA(B6:$O6)+COUNTBLANK(B6:$O6)))*SUM($B6:$O6)
> > > >
> > > >
> > > > COUNTIF(B6:$O6,0)
> > > > The COUNTIF function is counting the number of cells between B(row) and
> > > > O(row) that are equal to zero.
> > > >
> > > > COUNTBLANK(B6:$O6)
> > > > The COUNTBLANK function is counting the number of cells that are blank.
> > > >
> > > > COUNTA(B6:$O6)
> > > > Counts the number of non-blank cells.
> > > >
> > > > SUM($B6:$O6) - totals the values entered in B(row)-O(row)
> > > >
> > > > You should be able to put it together from here.
> > > > "Tara H" wrote:
> > > >
> > > > > I haven't had any luck with my previous query, so I did some trial and error,
> > > > > and produced a formula that gives me quite a nice graph that shows what I
> > > > > want. The problem is, I can't explain why.
> > > > >
> > > > > My data is in columns B to O, and in the fourteen columns to the right of
> > > > > that I have entered:
> > > > > =(1-(COUNTIF(B6:$O6,0)+COUNTBLANK(B6:$O6))/(COUNTA(B6:$O6)+COUNTBLANK(B6:$O6)))*SUM($B6:$O6)
> > > > >
> > > > > If I fill this across and down and produce a line graph of the results, it
> > > > > shows very clearly who is producing well and who isn't, but I can't present
> > > > > it without being able to tell what it does.
> > > > >
> > > > > I hope someone can give me a word or definition for what's happening here -
> > > > > if there's a simple function that will do the same or similar for me, even
> > > > > better!