| Author | Thread |
|
|
04/27/2006 03:05:57 AM · #1 |
I know this should be simple but I guess I'm having a brain freeze tonight. So I pleading for your help. Here is my formula problem.
EXAMPLE
I want to ADD
A2,A4,A6,A8,A10,A12 and divide it by the amount of cells in that range larger than zero. So if there is a positive number in A2,A4,A8 but not in A6,A10,A12 then the formula would divide by 3 not all cells.
Anyone have a suggestion?
Thanks
SDW
|
|
|
|
04/27/2006 03:07:29 AM · #2 |
| I forget ... but as I always say ... f1 is great :) |
|
|
|
04/27/2006 03:08:34 AM · #3 |
| When I joined DPC, they told me there would be no math. |
|
|
|
04/27/2006 03:11:10 AM · #4 |
Originally posted by Southern Gentleman:
I know this should be simple but I guess I'm having a brain freeze tonight. So I pleading for your help. Here is my formula problem.
EXAMPLE
I want to ADD
A2,A4,A6,A8,A10,A12 and divide it by the amount of cells in that range larger than zero. So if there is a positive number in A2,A4,A8 but not in A6,A10,A12 then the formula would divide by 3 not all cells.
Anyone have a suggestion?
Thanks
SDW |
So you want it only to add the positive numbers. Too tricky for me sorry
|
|
|
|
04/27/2006 03:13:03 AM · #5 |
Data Analyst here at your service
=(A2+A4+A6+A8+A10+A12)/(COUNTIF(A2:A12,">0"))
or
=(SUM(A2:A12)/(COUNTIF(A2:A12,">0"))))
Hope this helps
Edit - I have tried this and it works
Ben
Message edited by author 2006-04-27 03:16:24.
|
|
|
|
04/27/2006 03:13:39 AM · #6 |
I don't know how to do it, but you need a "test" or "if/else" function in there somewhere to evaluate your cells for non-zero status.
PS: And there it is!
Message edited by author 2006-04-27 03:14:39. |
|
|
|
04/27/2006 03:26:06 AM · #7 |
Originally posted by benhur:
Data Analyst here at your service
=(A2+A4+A6+A8+A10+A12)/(COUNTIF(A2:A12,">0"))
or
=(SUM(A2:A12)/(COUNTIF(A2:A12,">0"))))
Hope this helps
Edit - I have tried this and it works
Ben |
Not working for me. I have Excel 2000.
Here is my formula written the way you suggested. I'm sorry but in my first post I used a single column. It should be a row but skipping some columns in the row.
=(d2+f2+h2+j2+L2+n2)/(countif(d2+f2+h2+j2+L2+n2,"0"))
|
|
|
|
04/27/2006 03:26:08 AM · #8 |
edit:
Originally posted by Southern Gentleman:
Not working for me. I have Excel 2000. |
*bookmarks benhur as the excel guru*
*goes back to bleachers and waits for further results*
Message edited by author 2006-04-27 03:28:38. |
|
|
|
04/27/2006 03:32:39 AM · #9 |
Originally posted by benhur:
Data Analyst here at your service
=(A2+A4+A6+A8+A10+A12)/(COUNTIF(A2:A12,">0"))
or
=(SUM(A2:A12)/(COUNTIF(A2:A12,">0"))))
Hope this helps
Edit - I have tried this and it works
Ben |
wouldn't SUM(A2:A12) add all the sells between A2 and A12 inclusively -- not every other one as it should?
Also, wouldn't COUNTIF(A2:A12,">0") suffer similarly and count all the cells between A2 and A12 that are ">0" -- not every other one as it should?
David
|
|
|
|
04/27/2006 03:50:18 AM · #10 |
Is there any critical reason why it has to be every other cell? It would be best if all of the numbers to be operated on were grouped together in one row or column so a simple range would suffice (i.e. A1:A6). In this case, the formula would simply be as benhur stated:
=sum(A1:A6)/countif(A1:A6,">0")
[Note: You may get anomalous results if any of the cells have negative values. In this case, use:
=sumif(A1:A6,">0")/countif(A1:A6,">0")
]
Everything I can think of that would operate on even-rowed cells involves modulus arithmetic on the row numbers; I prefer keeping math to the actual values in the cells rather than the cell labels as it can get very unwieldy very quickly.
Originally posted by Southern Gentleman:
=(d2+f2+h2+j2+L2+n2)/(countif(d2+f2+h2+j2+L2+n2,"0"))
Not working for me. |
You forgot a ">" in the if-condition.
=(d2+f2+h2+j2+L2+n2)/(countif(d2+f2+h2+j2+L2+n2,">0"))
Message edited by author 2006-04-27 03:53:39. |
|
|
|
04/27/2006 04:07:32 AM · #11 |
Originally posted by Southern Gentleman:
Not working for me. I have Excel 2000.
Here is my formula written the way you suggested. I'm sorry but in my first post I used a single column. It should be a row but skipping some columns in the row.
=(d2+f2+h2+j2+L2+n2)/(countif(d2+f2+h2+j2+L2+n2,"0")) |
I am using office10 on one machine and XP on another and it works in both of them.
It would be easier if all the numbers were in consecutive cells, this way you could use (A1:A6)
Originally posted by David.C:
wouldn't SUM(A2:A12) add all the sells between A2 and A12 inclusively -- not every other one as it should?
Also, wouldn't COUNTIF(A2:A12,">0") suffer similarly and count all the cells between A2 and A12 that are ">0" -- not every other one as it should? |
Yes in my original formulea (A2:A12) will pick up all cells but it will only count or add together those with a positive value in them, as on the COUNTIF the if condition is only looking for positive values to count the total number to divide by.
|
|
|
|
04/27/2006 04:15:44 AM · #12 |
Originally posted by The_Itinerant:
[Note: You may get anomalous results if any of the cells have negative values. In this case, use:
=sumif(A1:A6,">0")/countif(A1:A6,">0") |
if you have a negative value and you want to include this in the dividing value then use the following by replacing the > in the COUNTIF condition to <>
=(SUM(A1:A6)/(COUNTIF(A1:A6,"<>0")))
> Greater than
< Less then
<> Greater than or Less then,
=> equal to or grater than
=< equal to or less than
<=> less than, equal to or great than (but then you would just use (COUNT(A1:A6) rather than COUNTIF)
Message edited by author 2006-04-27 04:16:33.
|
|
|
|
04/27/2006 04:24:57 AM · #13 |
Here is a screen print of what I'm doing in Excel.
Column A = Photographer and team (manual entry)
Column B = Photographer or team beginning avg. (manual entry)
Column C = Challenge entry's for week 1(manual entry)
Column D = Highest score of challenges entered for week 1 (formula /max)
Column E and F = same as C and D but week 2
Column G and H = same as C and D but week 3
Column I and J = same as C and D but week 4
Column K and L = same as C and D but week 5
Column M and N = same as C and D but week 6
Column C30:33 = Top 4 photographer scores during week 1
Now that I have all those formulas automated I need Column O formula to be automated instead of having to put in how many weeks the photographer entered, manually.
I hope this screen prints gives you an idea why I can't use column's or rows in sequence.
Thanks for any help,
SDW
|
|
|
|
04/27/2006 04:38:51 AM · #14 |
email me a copy and i will have a look and email it back
brawson021078@hotmail.com
|
|
|
|
04/27/2006 04:39:47 AM · #15 |
Originally posted by benhur:
email me a copy and i will have a look and email it back
brawson021078@hotmail.com |
OK, thanks
SDW
|
|
|
|
04/27/2006 04:51:41 AM · #16 |
Originally posted by benhur:
... Originally posted by David.C:
wouldn't SUM(A2:A12) add all the sells between A2 and A12 inclusively -- not every other one as it should?
Also, wouldn't COUNTIF(A2:A12,">0") suffer similarly and count all the cells between A2 and A12 that are ">0" -- not every other one as it should? |
Yes in my original formulea (A2:A12) will pick up all cells but it will only count or add together those with a positive value in them, as on the COUNTIF the if condition is only looking for positive values to count the total number to divide by. |
I mention it only because we are given no guarantee on the values of the uninvolved cells -- but since the table is relatively small hard-coding the cells makes more sense than evaluating which ones are involved.
|
|
|
|
04/27/2006 05:13:06 AM · #17 |
With a little modification you could make something like this work...
=SUM(D2,F2,H2,J2,L2,N2)/(SUMPRODUCT((D2:N2>0)*(A1:N1="High Score")))
You have to move the week designation to another cell though.
EDIT: Just to clarify the SUMPRODUCT is counting every cell that is greater than zero and where the cell above it has a value of "High Score".
Message edited by author 2006-04-27 05:16:29. |
|
|
|
04/27/2006 05:19:14 AM · #18 |
=IF(ISERROR(SUM(D2,F2,H2,J2,L2,N2)/(COUNTIF(D2,">0")+COUNTIF(F2,">0")+COUNTIF(H2,">0")+COUNTIF(J2,">0")+COUNTIF(L2,">0")+COUNTIF(N2,">0")))
,"",(SUM(D2,F2,H2,J2,L2,N2)/(COUNTIF(D2,">0")+COUNTIF(F2,">0")+COUNTIF(H2,">0")+COUNTIF(J2,">0")+COUNTIF(L2,">0")+COUNTIF(N2,">0"))))
this is the formula used it is long but it works and if it returns an error it will return a blank field so that it keeps the spreadsheet looking tidy.
Message edited by author 2006-04-27 05:44:46.
|
|
|
|
04/27/2006 05:20:59 AM · #19 |
Originally posted by Southern Gentleman:
Originally posted by benhur:
email me a copy and i will have a look and email it back
brawson021078@hotmail.com |
OK, thanks
SDW |
I have sent it back
|
|
|
|
04/27/2006 05:23:11 AM · #20 |
| Not to sound picky, or to make a stupid comment, but do you have the formula in CAPITAL LETTERS? If you actually used lower case letters it won't work. |
|
|
|
04/27/2006 05:27:30 AM · #21 |
Originally posted by TechnoShroom:
With a little modification you could make something like this work...
=SUM(D2,F2,H2,J2,L2,N2)/(SUMPRODUCT((D2:N2>0)*(A1:N1="High Score")))
You have to move the week designation to another cell though.
EDIT: Just to clarify the SUMPRODUCT is counting every cell that is greater than zero and where the cell above it has a value of "High Score". |
Made some alterations and it works good again did an IF(ISERROR(
=IF(ISERROR(SUM(D3,F3,H3,J3,L3,N3)/(SUMPRODUCT((D3:N3>0)*($D$2:$N$2="High Score")))),"",SUM(D3,F3,H3,J3,L3,N3)/(SUMPRODUCT((D3:N3>0)*($D$2:$N$2="High Score"))))
|
|
|
|
04/27/2006 05:28:09 AM · #22 |
Originally posted by NathanW:
Not to sound picky, or to make a stupid comment, but do you have the formula in CAPITAL LETTERS? If you actually used lower case letters it won't work. |
Excel will automatically change them to capitals if you type them in lower case
|
|
|
|
04/27/2006 05:37:34 AM · #23 |
Yea, this is trickier than it first looks. If the cells were contiguous (i.e. next to each other) it would be easy to do with the formula;
=AVERAGE(IF(A2:A7<>0, A2:A7,""))
I got that example from Excel help, and it works. You have to remember when you type it in to press 'CTRL-SHIFT-ENTER' instead of just 'ENTER' at the end, so it gets input as an array formula.
Anyway, I tried creating a named range of non-contiguous cells and slotting the range name into the above formula, but Excel just doesn't like it. Looks like the longhand approach is the only way (i.e. checking the contents of every cell, as I saw posted above) |
|
|
|
04/27/2006 05:47:07 AM · #24 |
Originally posted by jhonan:
Yea, this is trickier than it first looks. If the cells were contiguous (i.e. next to each other) it would be easy to do with the formula;
=AVERAGE(IF(A2:A7<>0, A2:A7,""))
I got that example from Excel help, and it works. You have to remember when you type it in to press 'CTRL-SHIFT-ENTER' instead of just 'ENTER' at the end, so it gets input as an array formula.
Anyway, I tried creating a named range of non-contiguous cells and slotting the range name into the above formula, but Excel just doesn't like it. Looks like the longhand approach is the only way (i.e. checking the contents of every cell, as I saw posted above) |
I tried using a "TEAM" name but it would not work, sometimes excel is a really good tool to work out things othertimes it is a pain in the a$$e
|
|
|
|
04/27/2006 08:46:09 AM · #25 |
If you don't mind hidden columns, this is pretty easy.
In this example, column a has the values in question.
create a column B where the cell values are "@if(a1 > 0,1,0)". A1 would be for row 1, a2 for row 2, etc. This will set a value in column b as either 0 or 1 based upon the value in column A.
Then, to do the calculation you wish to accomplish, you would just do a sum of the source range and divide it by the sum of the corresponding range in column b. Once you have this set up, just hide column b.
|
|
