How to Fix - average column with blanks and zeros base on two criteria in another single column?

average column with blanks and zeros base on two criteria in another single column

Hi,
 
I have the following situation:
 
Column X contains the number of process days and can be blank, zero or a number. Column A contains various transaction types. I need to Average Column X when Column A is New or Renew, but also discard blanks and zeros in column X to get an accurate average. 
 
 
Col A            Col X
New             10
Renew           0
Canc              3
Renew           5
Reins                 (reins is blank)
 
I need my result to be 7.5 (10+5/2)
 
Also, if the calculation is 0/0 prevent the #DIV/0! by displaying 0 instead.
 
I tried this formula but it doesn't like the "'s around "<>"&0 and when I remove them, then it still doesn't work.
 
 =IFERROR(SUM(SUMIF(X2:X10000,A2:A10000,"NEW","RENEW)))/SUM(COUNTIF(X2:X10000,"<>"&0),A2:A10000,"NEW","RENEW)),0)
 
Any help is greatly appreciated!!

Solutions to the Problem average column with blanks and zeros base on two criteria in another single column

Download Error Fixer for Free Now

(I did not include all the categories in my sample)

This is never a good idea and only serves to confuse the issue.
Abandon everything I've offered up to this point and start over.

The new, expanded requirements cannot be most efficiently fulfilled with a single style of formula and in any event, the originally supplied formula is not suited for multiple groups of lookup criteria.

              

If you want to find the average for a single match in column A but greater than zero in column X then an AVERAGEIFS() is your best recourse.
The formula in AB4 is,

=AVERAGEIFS(X2:X20,X2:X20,">"&0,A2:A20,AA4)

However, if you want to average the matching entries in column A to two (or more) possibilities, you should move to an
array formula.
The formula in AB2 is,

=AVERAGE(IF(X2:X20>0,IF(ISNUMBER(MATCH(A2:A20,"New","Renew",0)),X2:X20)))

...
which requires Ctrl+Shift+Enter rather than just Enter.
The formula in AB3 is,

=AVERAGE(IF(X2:X20>0,IF(ISNUMBER(MATCH(A2:A20,"Canc","Reins",0)),X2:X20)))

...
which requires Ctrl+Shift+Enter rather than just Enter.

In this method we will try to Boot in Safe mode with Networking and see if the error still persists.

• Search "MSCONFIG" and Open.
• Click on Boot Tab
• Tick Safe Boot in Boot Option
• Select Network.
• Click Apply and OK.
• Reboot your System.

Recommended Method to Fix the Problem: average column with blanks and zeros base on two criteria in another single column:

How to Fix average column with blanks and zeros base on two criteria in another single column with SmartPCFixer?

1. Download Error Fixer. Install it on your system.

2. After the scan is finished, you can see the errors and problems which need to be fixed.

3. When the Fixing part is done, your computer has been speeded up and the errors have been fixed

Related: AMD Radeon HD 7800M Win8 not working [Anwsered],I can access the internet, get on facebook and get to hotmail, but I can't play games on facebook and I can't open or respond to my e-mails,I keep getting this Media Player error when I log on my computer. [Anwsered],[Anwsered] System Hanging on shutdown and restart,Unable to get the Vlookup property of the WorksheetFunction class,Solution to Error: Error: "0x81000032 make sure the C: drive is online and set to NTFS" when trying to backup to external hard drive.
,Troubleshoot:External Hard Drive not listed in Windows 7 backup wizard Error
,I'm always being signed off so annoying Tech Support
,Solution to Problem: Impossible to use Internet Explorer! I keep getting the same error message every time i try to use IE.
,Solution to Problem: Referencing data in another file
,Troubleshoot:Error: "0x81000032 make sure the C: drive is online and set to NTFS" when trying to backup to external hard drive. Error,External Hard Drive not listed in Windows 7 backup wizard Tech Support,Tech Support: I'm always being signed off so annoying,Solution to Problem: Impossible to use Internet Explorer! I keep getting the same error message every time i try to use IE.,Referencing data in Access using Excel [Anwsered],Need Best Way To Present Data [Anwsered],Same question but for windows 7 home edition,sometimes fullscreen won't activate [Solved],Solution to Error: We bought a new computer with windows 7 and it is constantly freezing. How do we fix this?,Solution to Error: Windows 8 update crash (2013-07-22)
Read More: [Solution] Atheros AR9485WB-EG shuts down,How to Fix Error - Averaging Percentages with the error #DIV\/0!?,Automatically divide cell by a number Tech Support,How to Fix - Bacckup- It says backup complete, but some files skipped. backing up on DVD\/R?,How to Fix Problem - ati catalyst control center?,application not found error,any problems in a team where one has Windows XP and the other has Windows 7?,Application/Object-Defined Error,An Excel formula question where hours are totalled and cumulating,Anyone know the hardware email?