Please explain function/formula
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!
|