Index(frequency((if

E

EXC19

New Member
Joined
Jul 18, 2017
Messages
2
Hello,

I am working trying to get the average of non-continuous cells that are >0. I have looked up this topic and used advice found there previously but I am not getting the results.

This is the current formula I am using: =IFERROR(SUM(IF(AF2>0,AE2)+IF(AY2>0,AX2)+IF(BR2>0,BQ2)+IF(CK2>0,CJ2)+IF(DC2>0,DC2)+IF(DV2>0,DV2)+IF(EM2>0,EO2))/INDEX(FREQUENCY((IF(AF2>0,AE2),IF(AY2>0,AX2),IF(BR2>0,BQ2),IF(CK2>0,CJ2),IF(DC2>0,DC2),IF(DV2>0,DV2),IF(EM2>0,EO2)),0),2),"0")

[TABLE="width: 816"]
<tbody>[TR]
[TD="class: xl71, width: 64, bgcolor: #FFD966"]Column K
[/TD]
[TD="class: xl71, width: 64, bgcolor: #FFD966"]Column L
[/TD]
[TD="class: xl71, width: 64, bgcolor: #FFD966"]Column M
[/TD]
[TD="class: xl72, width: 64, bgcolor: #FCE4D6"]Column AE
[/TD]
[TD="class: xl72, width: 64, bgcolor: #FCE4D6"]Column AF
[/TD]
[TD="class: xl75, width: 64, bgcolor: #DDEBF7"]Column AX
[/TD]
[TD="class: xl75, width: 64, bgcolor: #DDEBF7"]Column AY
[/TD]
[TD="class: xl76, width: 64, bgcolor: #FFF2CC"]Column BQ
[/TD]
[TD="class: xl76, width: 64, bgcolor: #FFF2CC"]Column BR
[/TD]
[TD="class: xl75, width: 64, bgcolor: #DDEBF7"]Column CJ
[/TD]
[TD="class: xl75, width: 64, bgcolor: #DDEBF7"]Column CK
[/TD]
[TD="class: xl77, width: 64, bgcolor: #CCCCFF"]Column DC
[/TD]
[TD="class: xl77, width: 64, bgcolor: #CCCCFF"]Column DD
[/TD]
[TD="class: xl78, width: 64, bgcolor: #FFBDBD"]Column DV
[/TD]
[TD="class: xl78, width: 64, bgcolor: #FFBDBD"]Column DW
[/TD]
[TD="class: xl79, width: 64, bgcolor: #D1FFA3"]Column EO
[/TD]
[TD="class: xl79, width: 64, bgcolor: #D1FFA3"]Column EP
[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 64, bgcolor: #FFD966"] Average Exp Liab Cost per Unit
[/TD]
[TD="class: xl71, width: 64, bgcolor: #FFD966"]Average Ren Liab Cost per Unit
[/TD]
[TD="class: xl71, width: 64, bgcolor: #FFD966"]Average Liab Cost per Unit Rate Change
[/TD]
[TD="class: xl72, width: 64, bgcolor: #FCE4D6"]Exp Liab Cost per Unit
[/TD]
[TD="class: xl72, width: 64, bgcolor: #FCE4D6"]Ren Liab Cost Per Unit
[/TD]
[TD="class: xl75, width: 64, bgcolor: #DDEBF7"]Exp Liab Cost per Unit
[/TD]
[TD="class: xl75, width: 64, bgcolor: #DDEBF7"]Ren Liab Cost Per Unit
[/TD]
[TD="class: xl76, width: 64, bgcolor: #FFF2CC"]Exp Liab Cost per Unit
[/TD]
[TD="class: xl76, width: 64, bgcolor: #FFF2CC"]Ren Liab Cost Per Unit
[/TD]
[TD="class: xl75, width: 64, bgcolor: #DDEBF7"]Exp Liab Cost per Unit
[/TD]
[TD="class: xl75, width: 64, bgcolor: #DDEBF7"]Ren Liab Cost Per Unit
[/TD]
[TD="class: xl77, width: 64, bgcolor: #CCCCFF"]Exp Liab Cost per Unit
[/TD]
[TD="class: xl77, width: 64, bgcolor: #CCCCFF"]Ren Liab Cost Per Unit
[/TD]
[TD="class: xl78, width: 64, bgcolor: #FFBDBD"]Exp Liab Cost per Unit
[/TD]
[TD="class: xl78, width: 64, bgcolor: #FFBDBD"]Ren Liab Cost Per Unit
[/TD]
[TD="class: xl79, width: 64, bgcolor: #D1FFA3"]Exp Liab Cost per Unit
[/TD]
[TD="class: xl79, width: 64, bgcolor: #D1FFA3"]Ren Liab Cost Per Unit
[/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: #FFF2CC"]1695
[/TD]
[TD="class: xl82, bgcolor: #FFF2CC"]1572
[/TD]
[TD="class: xl67"]-7%
[/TD]
[TD="class: xl73, bgcolor: transparent"]3219
[/TD]
[TD="class: xl73, bgcolor: transparent"]2973
[/TD]
[TD="class: xl73, bgcolor: transparent"]2952
[/TD]
[TD="class: xl73, bgcolor: transparent"]2727
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]1133
[/TD]
[TD="class: xl73, bgcolor: transparent"]1048
[/TD]
[TD="class: xl73, bgcolor: transparent"]1088
[/TD]
[TD="class: xl73, bgcolor: transparent"]1007
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]85
[/TD]
[TD="class: xl73, bgcolor: transparent"]103
[/TD]
[/TR]
[TR]
[TD="class: xl85, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl85, bgcolor: #F2F2F2"]5055
[/TD]
[TD="class: xl84, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]5029
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]5055
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #D6DCE4"]3980
[/TD]
[TD="class: xl90, bgcolor: #D6DCE4"]2373
[/TD]
[TD="class: xl89"]-40%
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]6108
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]4554
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]5585
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]247
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]191
[/TD]
[/TR]
[TR]
[TD="class: xl85, bgcolor: #F2F2F2"]1066
[/TD]
[TD="class: xl85, bgcolor: #F2F2F2"]1100
[/TD]
[TD="class: xl84, bgcolor: #F2F2F2"]3%
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]1496
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]1543
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]1482
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]1530
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]219
[/TD]
[TD="class: xl86, bgcolor: #F2F2F2"]227
[/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl90, bgcolor: #D6DCE4"]2116
[/TD]
[TD="class: xl89, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]2446
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]1609
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]1996
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]1572
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]1906
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[TD="class: xl91, bgcolor: #D6DCE4"]0
[/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: #FFF2CC"]0
[/TD]
[TD="class: xl82, bgcolor: #FFF2CC"]1968
[/TD]
[TD="class: xl67, bgcolor: #FFF2CC"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]2117
[/TD]
[TD="class: xl73, bgcolor: transparent"]2899
[/TD]
[TD="class: xl73, bgcolor: transparent"]2039
[/TD]
[TD="class: xl73, bgcolor: transparent"]1708
[/TD]
[TD="class: xl73, bgcolor: transparent"]1291
[/TD]
[TD="class: xl73, bgcolor: transparent"]1296
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: #FFF2CC"]0
[/TD]
[TD="class: xl82, bgcolor: #FFF2CC"]2362
[/TD]
[TD="class: xl67, bgcolor: #FFF2CC"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]2733
[/TD]
[TD="class: xl73, bgcolor: transparent"]3767
[/TD]
[TD="class: xl73, bgcolor: transparent"]2545
[/TD]
[TD="class: xl73, bgcolor: transparent"]2233
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]2579
[/TD]
[TD="class: xl73, bgcolor: transparent"]2594
[/TD]
[TD="class: xl73, bgcolor: transparent"]718
[/TD]
[TD="class: xl73, bgcolor: transparent"]852
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: #FFF2CC"]4013
[/TD]
[TD="class: xl82, bgcolor: #FFF2CC"]4012
[/TD]
[TD="class: xl67"]0%
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]7562
[/TD]
[TD="class: xl73, bgcolor: transparent"]7468
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]463
[/TD]
[TD="class: xl73, bgcolor: transparent"]556
[/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: #FFF2CC"]0
[/TD]
[TD="class: xl82, bgcolor: #FFF2CC"]2985
[/TD]
[TD="class: xl67, bgcolor: #FFF2CC"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]3791
[/TD]
[TD="class: xl73, bgcolor: transparent"]3707
[/TD]
[TD="class: xl73, bgcolor: transparent"]2312
[/TD]
[TD="class: xl73, bgcolor: transparent"]2262
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: #FFF2CC"]0
[/TD]
[TD="class: xl82, bgcolor: #FFF2CC"]2268
[/TD]
[TD="class: xl67, bgcolor: #FFF2CC"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]2075
[/TD]
[TD="class: xl73, bgcolor: transparent"]2268
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: #FFF2CC"]0
[/TD]
[TD="class: xl82, bgcolor: #FFF2CC"]816
[/TD]
[TD="class: xl67, bgcolor: #FFF2CC"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]768
[/TD]
[TD="class: xl73, bgcolor: transparent"]1129
[/TD]
[TD="class: xl73, bgcolor: transparent"]463
[/TD]
[TD="class: xl73, bgcolor: transparent"]660
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]576
[/TD]
[TD="class: xl73, bgcolor: transparent"]660
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: #FFF2CC"]0
[/TD]
[TD="class: xl82, bgcolor: #FFF2CC"]1994
[/TD]
[TD="class: xl67, bgcolor: #FFF2CC"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]1961
[/TD]
[TD="class: xl73, bgcolor: transparent"]1994
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]0
[/TD]
[/TR]
</tbody>[/TABLE]


I only want to include matching data in the averages. So if any of the Exp Liab Cost per Unit columns have a 0 but the Ren Liab Cost per Unit column of the same color has a number it will not be included in the average, and vice versa for the Ren vs Exp. For example:

Row 3: The average should only include Columns AE+EO and be divisible by 2 but it is including Column AX and is dividing by 3.

What can I do so it only calculates cells with a number in the Exp and Ren columns if they both contain numbers above 0? I need to average this way as well.

Thanks for you help.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Welcome to the forum.

Try this:

=IFERROR(AVERAGE(IF(SUBTOTAL(6,OFFSET(AE2:AF2,0,{0,19,38,57,76,95,114})),SUBTOTAL(9,OFFSET(AE2,0,{0,19,38,57,76,95,114})))),0)

The numbers in the internal array constants (in red) are the offsets from your first column. They're all 19 columns apart in this example.
 
Upvote 0
Or, Av Exp Liab Cost: =AVERAGEIFS(AE2:EO2,AE$1:EO$1,"Exp Liab Cost per Unit",AF2:EP2,">0")

e.g. in row 3 will return 3,177.5 = (6,108+247)/2, rather than your 3,980 = (6,108 + 5,585 + 247)/3
 
Upvote 0
I just came up with a shorter version:

=IFERROR(AVERAGE(IF(MOD(COLUMN(AE2:EO2),19)=12,IF(AE2:EO2*AF2:EP2,AE2:EO2))),0)

which is based on the fact that the columns are 19 apart.

Only to find that StephenCrump came up with yet a nicer version!
However, I think the OP wants both the Exp and Ren columns to be >0, so your formula would be:

=AVERAGEIFS(AE2:EO2,AE$1:EO$1,"Exp Liab Cost per Unit",AF2:EP2,">0",AE2:EO2,">0")

and possibly an IFERROR to prevent #DIV/0 if there are no matches.
 
Upvote 0
You must log in or register to reply here.

Similar threads

cmschmitz24
  • Question Question
Macro Help - Move/Delete based on cell criteria
2
Replies
13
Views
2K
DanteAmor
DanteAmor
M
  • Question Question
VBA Code to post numbers instead of text into excel spread sheet
Replies
3
Views
715
Tinbendr
Tinbendr
L
  • Question Question
Need Help - Workload Capacity
Replies
2
Views
2K
lmendels
L
S
  • Question Question
Cleaning Up VBA Code
Replies
5
Views
2K
Tazguy37
Tazguy37

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top