Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Averaging with zeros NOT
{=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,Prov!$R$2:$R$29 78))}
Above is a formula that succesfully averages what I want. However sometime the Prov!range r2:R2978 contains zeros. How can I eliminate those zeros from the average? The first part of the formula with the cell reference is looking up and match a name before performin the average.. TIA Tom |
#2
|
|||
|
|||
=AVERAGE(IF((Prov!$A$2:$A$2978=$A2)*(Prov!$R$2:$R$ 2978<0),Prov!$R$2:$R$2978
)) still an array formula -- HTH RP (remove nothere from the email address if mailing direct) "Tom" wrote in message ups.com... {=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,Prov!$R$2:$R$29 78))} Above is a formula that succesfully averages what I want. However sometime the Prov!range r2:R2978 contains zeros. How can I eliminate those zeros from the average? The first part of the formula with the cell reference is looking up and match a name before performin the average.. TIA Tom |
#3
|
|||
|
|||
Add another if statement right before you do the average:
=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,IF(Prov!$R$2:$R$ 2978=0,"",Prov!$R$2:$R$2978),"")) -- Regards, Dave "Tom" wrote: {=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,Prov!$R$2:$R$29 78))} Above is a formula that succesfully averages what I want. However sometime the Prov!range r2:R2978 contains zeros. How can I eliminate those zeros from the average? The first part of the formula with the cell reference is looking up and match a name before performin the average.. TIA Tom |
#4
|
|||
|
|||
Hi!
Try this: Array entered: =AVERAGE(IF((Prov!$A$2:$A$2978=$A2)*(Prov!$R$2:$R$ 2978<0),Prov!$R$2:$R$2978)) Biff "Tom" wrote in message ups.com... {=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,Prov!$R$2:$R$29 78))} Above is a formula that succesfully averages what I want. However sometime the Prov!range r2:R2978 contains zeros. How can I eliminate those zeros from the average? The first part of the formula with the cell reference is looking up and match a name before performin the average.. TIA Tom |
#5
|
|||
|
|||
You could create another column of data that read's the column you wish to average like this {=if(A2=0,"",A2). This will effectively replace the zeros with blanks, which are not included as zero values in an average. Then you can change the column you wish to average with the new column. There'd be a myriad of more complicated and VBA-involved solutions, but this is quick and easy. -- Excel_Geek ------------------------------------------------------------------------ Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423 View this thread: http://www.excelforum.com/showthread...hreadid=396927 |
#6
|
|||
|
|||
Tom,
=SUMIF(A2:A2978,A2,R2:R2978)/SUMPRODUCT((A2:A2978=A2)*(R2:R2978<0)) Array-entering not necessary. -- Earl Kiosterud www.smokeylake.com "Tom" wrote in message ups.com... {=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,Prov!$R$2:$R$29 78))} Above is a formula that succesfully averages what I want. However sometime the Prov!range r2:R2978 contains zeros. How can I eliminate those zeros from the average? The first part of the formula with the cell reference is looking up and match a name before performin the average.. TIA Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CSV File - Leading Zeros | Excel Discussion (Misc queries) | |||
Creating formulas that allow the solutions to start with zeros. | Excel Discussion (Misc queries) | |||
How do I force leading zeros in an Excel cell? | Excel Discussion (Misc queries) | |||
Averaging noncontiguous numbers ignoring zeros? | Excel Worksheet Functions | |||
Averaging, ignoring zeros | Excel Worksheet Functions |