Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please explain function/formula
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please explain function/formula
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please explain function/formula
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please explain function/formula
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please explain function/formula
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. "tim m" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What function/formula should I use? | Excel Worksheet Functions | |||
explain purpose of {} | Excel Discussion (Misc queries) | |||
Explain return of #N/A | Excel Worksheet Functions | |||
Please Explain in Lamen's Terms | Excel Worksheet Functions | |||
Explain these please | Excel Worksheet Functions |