Please explain function/formula

[Guest]

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!

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

Hi Tim,

My original query is at:
http://www.microsoft.com/office/com...ions&mid=f042f7d7-0fc5-4129-9698-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

 

[Guest]

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.

 

[Guest]

Not exactly strictly defined, but basically one of two things happen,
production starts out good and stays the same or goes higher, with possibly
the odd 'off'week, or it starts out good and steadily decreases until it hits
0 and then stays there. The idea is to identify the second case as early as
possible.