| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Non Adjacent percent average total
Ok, I don't know what to do to get the cells with I want to average that are
currently blank to show a blank cell if there is no number or a zero in the cells, I think this just sounds confusing. Anywho, this is the formula I have and it's showing a blank cell but that's all it does, or it errors and I can't make it an array. =IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY530))=0,"",AVERAGE(IF((MOD(ROW(D53:CY53),9 )=0)*(D53:CY53<0),D53:CY53))) Anyone know what the problem is? Thanks for the help. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Non Adjacent percent average total
This part is your problem
MOD(ROW(D53:CY53),9) as there is only one row, row 53, and it alwqays evalutes to 0. You probably want =IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=0),--(D53:CY530))=0,"",AVERAGE(IF ((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53<0),D53:CY5 3))) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "tearingoutmyhair" wrote in message ... Ok, I don't know what to do to get the cells with I want to average that are currently blank to show a blank cell if there is no number or a zero in the cells, I think this just sounds confusing. Anywho, this is the formula I have and it's showing a blank cell but that's all it does, or it errors and I can't make it an array. =IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY530))=0,"",AVERAGE(IF((M OD(ROW(D53:CY53),9)=0)*(D53:CY53<0),D53:CY53))) Anyone know what the problem is? Thanks for the help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Non Adjacent percent average total
It gave me an error, and wouldn't let me enter it.
Well all the totals that's I'm trying to get the average of are in row 53, so I figured I wanted ROW. I got that whole thing off of some other comment in this place though, figured I'd give it a shot. "Bob Phillips" wrote: This part is your problem MOD(ROW(D53:CY53),9) as there is only one row, row 53, and it alwqays evalutes to 0. You probably want =IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=0),--(D53:CY530))=0,"",AVERAGE(IF ((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53<0),D53:CY5 3))) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "tearingoutmyhair" wrote in message ... Ok, I don't know what to do to get the cells with I want to average that are currently blank to show a blank cell if there is no number or a zero in the cells, I think this just sounds confusing. Anywho, this is the formula I have and it's showing a blank cell but that's all it does, or it errors and I can't make it an array. =IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY530))=0,"",AVERAGE(IF((M OD(ROW(D53:CY53),9)=0)*(D53:CY53<0),D53:CY53))) Anyone know what the problem is? Thanks for the help. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Non Adjacent percent average total
Why are you doing the MOD then? That will exclude certain items that don't
match the MOD criteria, which in the formula that you presented was all of them. Why is =AVERAGE(D53:CY53) not sufficient? -- HTH Bob Phillips (remove xxx from email address if mailing direct) "tearingoutmyhair" wrote in message ... It gave me an error, and wouldn't let me enter it. Well all the totals that's I'm trying to get the average of are in row 53, so I figured I wanted ROW. I got that whole thing off of some other comment in this place though, figured I'd give it a shot. "Bob Phillips" wrote: This part is your problem MOD(ROW(D53:CY53),9) as there is only one row, row 53, and it alwqays evalutes to 0. You probably want =IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=0),--(D53:CY530))=0,"",AVERAGE(IF ((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53<0),D53:CY5 3))) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "tearingoutmyhair" wrote in message ... Ok, I don't know what to do to get the cells with I want to average that are currently blank to show a blank cell if there is no number or a zero in the cells, I think this just sounds confusing. Anywho, this is the formula I have and it's showing a blank cell but that's all it does, or it errors and I can't make it an array. =IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY530))=0,"",AVERAGE(IF((M OD(ROW(D53:CY53),9)=0)*(D53:CY53<0),D53:CY53))) Anyone know what the problem is? Thanks for the help. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Non Adjacent percent average total
These are the nonadjecent cells that I need to get the total average of.
D53,M53,V53,AE53,AN53,AW53,BF53,BO53,BX53,CG53,CP5 3,CY53 The =AVERAGE wont work because it's for future years and those months have no numbers entered yet so have blank cells that I'm trying to average. I get the #DIV/0! when I insert that formula because of the empty cells. "Bob Phillips" wrote: Why are you doing the MOD then? That will exclude certain items that don't match the MOD criteria, which in the formula that you presented was all of them. Why is =AVERAGE(D53:CY53) not sufficient? -- HTH Bob Phillips (remove xxx from email address if mailing direct) "tearingoutmyhair" wrote in message ... It gave me an error, and wouldn't let me enter it. Well all the totals that's I'm trying to get the average of are in row 53, so I figured I wanted ROW. I got that whole thing off of some other comment in this place though, figured I'd give it a shot. "Bob Phillips" wrote: This part is your problem MOD(ROW(D53:CY53),9) as there is only one row, row 53, and it alwqays evalutes to 0. You probably want =IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=0),--(D53:CY530))=0,"",AVERAGE(IF ((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53<0),D53:CY5 3))) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "tearingoutmyhair" wrote in message ... Ok, I don't know what to do to get the cells with I want to average that are currently blank to show a blank cell if there is no number or a zero in the cells, I think this just sounds confusing. Anywho, this is the formula I have and it's showing a blank cell but that's all it does, or it errors and I can't make it an array. =IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY530))=0,"",AVERAGE(IF((M OD(ROW(D53:CY53),9)=0)*(D53:CY53<0),D53:CY53))) Anyone know what the problem is? Thanks for the help. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Non Adjacent percent average total
What you then is
=IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=4),--(D53:CY530))=0,"", AVERAGE(IF((MOD(COLUMN(D53:CY53),9)=4)*(D53:CY53< 0),D53:CY53))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "tearingoutmyhair" wrote in message ... These are the nonadjecent cells that I need to get the total average of. D53,M53,V53,AE53,AN53,AW53,BF53,BO53,BX53,CG53,CP5 3,CY53 The =AVERAGE wont work because it's for future years and those months have no numbers entered yet so have blank cells that I'm trying to average. I get the #DIV/0! when I insert that formula because of the empty cells. "Bob Phillips" wrote: Why are you doing the MOD then? That will exclude certain items that don't match the MOD criteria, which in the formula that you presented was all of them. Why is =AVERAGE(D53:CY53) not sufficient? -- HTH Bob Phillips (remove xxx from email address if mailing direct) "tearingoutmyhair" wrote in message ... It gave me an error, and wouldn't let me enter it. Well all the totals that's I'm trying to get the average of are in row 53, so I figured I wanted ROW. I got that whole thing off of some other comment in this place though, figured I'd give it a shot. "Bob Phillips" wrote: This part is your problem MOD(ROW(D53:CY53),9) as there is only one row, row 53, and it alwqays evalutes to 0. You probably want =IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=0),--(D53:CY530))=0,"",AVERAGE(IF ((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53<0),D53:CY5 3))) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "tearingoutmyhair" wrote in message ... Ok, I don't know what to do to get the cells with I want to average that are currently blank to show a blank cell if there is no number or a zero in the cells, I think this just sounds confusing. Anywho, this is the formula I have and it's showing a blank cell but that's all it does, or it errors and I can't make it an array. =IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY530))=0,"",AVERAGE(IF((M OD(ROW(D53:CY53),9)=0)*(D53:CY53<0),D53:CY53))) Anyone know what the problem is? Thanks for the help. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Non Adjacent percent average total
More generically, you could use
=IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=MIN(COLUMN(D53:CY53))),--(D53:CY53 0))=0,"", AVERAGE(IF((MOD(COLUMN(D53:CY53),9)=MIN(COLUMN(D53 :CY53)))*(D53:CY53<0),D53 :CY53))) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "tearingoutmyhair" wrote in message ... These are the nonadjecent cells that I need to get the total average of. D53,M53,V53,AE53,AN53,AW53,BF53,BO53,BX53,CG53,CP5 3,CY53 The =AVERAGE wont work because it's for future years and those months have no numbers entered yet so have blank cells that I'm trying to average. I get the #DIV/0! when I insert that formula because of the empty cells. "Bob Phillips" wrote: Why are you doing the MOD then? That will exclude certain items that don't match the MOD criteria, which in the formula that you presented was all of them. Why is =AVERAGE(D53:CY53) not sufficient? -- HTH Bob Phillips (remove xxx from email address if mailing direct) "tearingoutmyhair" wrote in message ... It gave me an error, and wouldn't let me enter it. Well all the totals that's I'm trying to get the average of are in row 53, so I figured I wanted ROW. I got that whole thing off of some other comment in this place though, figured I'd give it a shot. "Bob Phillips" wrote: This part is your problem MOD(ROW(D53:CY53),9) as there is only one row, row 53, and it alwqays evalutes to 0. You probably want =IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=0),--(D53:CY530))=0,"",AVERAGE(IF ((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53<0),D53:CY5 3))) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "tearingoutmyhair" wrote in message ... Ok, I don't know what to do to get the cells with I want to average that are currently blank to show a blank cell if there is no number or a zero in the cells, I think this just sounds confusing. Anywho, this is the formula I have and it's showing a blank cell but that's all it does, or it errors and I can't make it an array. =IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY530))=0,"",AVERAGE(IF((M OD(ROW(D53:CY53),9)=0)*(D53:CY53<0),D53:CY53))) Anyone know what the problem is? Thanks for the help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot table question: How to display total and percent for data simultaneouly | Excel Discussion (Misc queries) | |||
| matching the column b(sku)c(count)with A | Excel Worksheet Functions | |||
| Retrieving non adjacent data from other workbooks? | Excel Worksheet Functions | |||
| Limit or Exclude cells in Average and Sum formula | Excel Worksheet Functions | |||
| What is this kind of average called? | Excel Worksheet Functions | |||
Linear Mode
