Conditional average (index?)

mischifous

New Member
Joined
Mar 14, 2016
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Hi excel wizards,


I have two large tables, I'm wanting to create a column, that will find the average of points in table 2, based on if the corresponding value in table 1 meets a certain condition. Is this possible to

For example,

"TABLE1"
JOHNSALLYMAX
day 135%22%55%
day 228%34%52%

"TABLE2"
JOHNSALLYMAX
day110912
day28511


In a single column, i want to calculate the average for each day where only the values that are >30% in table 1 are included to calculate the average:

thus, a new table/column that has the following values
day 1= average('TABLE2'!B2,'TABLE2'!D2)
day2 = average('TABLE2'!C2,'TABLE2'!D2)

(my data is actually much larger than this but it is in this format) There are also sometimes values in the format of "-" in TABL1.

As always, i really appreciate you guys' wizardry. This community has helped me so much over the years.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Great. If format and headers in table 1 and 2 are identical, this should work

Book1
ABCDEFGHIJKL
1JOHNSALLYMAXJOHNSALLYMAXAverage
2day 135%22%55%day110912day 111
3day 228%34%52%day28511day 28
Sheet1
Cell Formulas
RangeFormula
L2:L3L2=AVERAGEIF(B2:D2,">0.3",G2:I2)
 
Upvote 0
You can al
Hi excel wizards,


I have two large tables, I'm wanting to create a column, that will find the average of points in table 2, based on if the corresponding value in table 1 meets a certain condition. Is this possible to

For example,

"TABLE1"
JOHNSALLYMAX
day 135%22%55%
day 228%34%52%

"TABLE2"
JOHNSALLYMAX
day110912
day28511


In a single column, i want to calculate the average for each day where only the values that are >30% in table 1 are included to calculate the average:

thus, a new table/column that has the following values
day 1= average('TABLE2'!B2,'TABLE2'!D2)
day2 = average('TABLE2'!C2,'TABLE2'!D2)

(my data is actually much larger than this but it is in this format) There are also sometimes values in the format of "-" in TABL1.

As always, i really appreciate you guys' wizardry. This community has helped me so much over the years.

How about

Book7
ABCDEFGHIJKL
1JOHNSALLYMAXJOHNSALLYMAXDayOutput
2day 135%30%55%day110912day 111
3day 228%34%52%day28511day 28
Sheet1
Cell Formulas
RangeFormula
L2:L3L2=AVERAGE(IF($B2:$D2>0.3,$G2:$I2))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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