Average of first 4 cells that have a value

M

Mariyka1

New Member
Joined
Nov 1, 2016
Messages
44
I have a worksheet that looks similar to the below - the data-set is very large so I just need to be able to create a single formula to take the average of the first 4 values in that row. some at the beginning are blank - I want the formula to ignore blank and take the average of the first four values. I know there is an average formula that ignores blanks - but I just don't know how I can constrict this formula to only take the first 4 values down to about 500k of rows.


[TABLE="width: 704"]
<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]January[/TD]
[TD="width: 64"]February[/TD]
[TD="width: 64"]March[/TD]
[TD="width: 64"]April[/TD]
[TD="width: 64"]May[/TD]
[TD="width: 64"]June[/TD]
[TD="width: 64"]July[/TD]
[TD="width: 64"]August[/TD]
[TD="width: 64"]September[/TD]
[TD="width: 64"]November[/TD]
[TD="width: 64"]December[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]20[/TD]
[TD="class: xl66, align: right"]10[/TD]
[TD="class: xl66, align: right"]40[/TD]
[TD="class: xl66, align: right"]70[/TD]
[TD="class: xl66, align: right"]70[/TD]
[TD="class: xl66, align: right"]30[/TD]
[TD="class: xl66, align: right"]60[/TD]
[TD="class: xl66, align: right"]70[/TD]
[TD="class: xl66, align: right"]30[/TD]
[TD="class: xl66, align: right"]20[/TD]
[TD="class: xl66, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66, align: right"]30[/TD]
[TD="class: xl66, align: right"]50[/TD]
[TD="class: xl66, align: right"]80[/TD]
[TD="class: xl66, align: right"]250[/TD]
[TD="class: xl66, align: right"]80[/TD]
[TD="class: xl66, align: right"]50[/TD]
[TD="class: xl66, align: right"]250[/TD]
[TD="class: xl66, align: right"]80[/TD]
[TD="class: xl66, align: right"]20[/TD]
[TD="class: xl66, align: right"]50[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]50[/TD]
[TD="class: xl66, align: right"]40[/TD]
[TD="class: xl66, align: right"]10[/TD]
[TD="class: xl66, align: right"]50[/TD]
[TD="class: xl66, align: right"]40[/TD]
[TD="class: xl66, align: right"]40[/TD]
[TD="class: xl66, align: right"]30[/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl66, align: right"]40[/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl66, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66, align: right"]30[/TD]
[TD="class: xl66, align: right"]120[/TD]
[TD="class: xl66, align: right"]10[/TD]
[TD="class: xl66, align: right"]120[/TD]
[TD="class: xl66, align: right"]40[/TD]
[TD="class: xl66, align: right"]50[/TD]
[TD="class: xl66, align: right"]20[/TD]
[TD="class: xl66, align: right"]40[/TD]
[TD="class: xl66, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66, align: right"]50[/TD]
[TD="class: xl66, align: right"]50[/TD]
[TD="class: xl66, align: right"]40[/TD]
[TD="class: xl66, align: right"]50[/TD]
[TD="class: xl66, align: right"]50[/TD]
[TD="class: xl66, align: right"]30[/TD]
[TD="class: xl66, align: right"]50[/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66, align: right"]50[/TD]
[TD="class: xl66, align: right"]40[/TD]
[TD="class: xl66, align: right"]120[/TD]
[TD="class: xl66, align: right"]50[/TD]
[TD="class: xl66, align: right"]40[/TD]
[TD="class: xl66, align: right"]40[/TD]
[TD="class: xl66, align: right"]20[/TD]
[/TR]
</tbody>[/TABLE]
you help is very much appreciated!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Array formula:

=AVERAGE(INDEX($A2:$K2, SMALL(IF($A2:$K2>0, COLUMN($A2:$K2), ""), 4)):INDEX($A2:$K2, SMALL(IF($A2:$K2>0, COLUMN($A2:$K2), ""), 1)))

Ctrl+Shift+Enter
 
Upvote 0
Another option:
Also an array formula that must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDEFGHIJKL
1JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberNovemberDecemberAverage
2201040707030607030201035
33050802508050250802050102.5
4504010504040300400037.5
5301201012040502040070
65050405050305047.5
750401205040402065
Sheet
 
Upvote 0
Thank you, although unfortunately it did not give me the average of the first 4 values in each row - it was closer to the average of the first 38 columns (out of a total of 117 columns)
 
Upvote 0
Thank you!! this worked perfectly :-)


AhoyNC said:
Another option:
Also an array formula that must be entered with CTRL-SHIFT-ENTER.

ABCDEFGHIJKL

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:77px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:85px;"><col style="width:77px;"><col style="width:79px;"><col style="width:66px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]January[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]February[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]March[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]April[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]May[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]June[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]July[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]August[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]September[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]November[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]December[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Average[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]35[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: center"]30[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]50[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]102.5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]37.5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: center"]30[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]70[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: center"]50[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]50[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]47.5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: center"]50[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]65[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
L2{=AVERAGE(OFFSET(A2:K2,,MATCH(TRUE,ISNUMBER(A2:K2),0)-1,,4))}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
You're welcome. Thanks for the feedback. I tried the formula by Tom_Jones and it worked for me.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
If Formula
Replies
2
Views
60
dreid1011
dreid1011
J
  • Solved
IF and AND formulas for multiple criteria
Replies
6
Views
642
Peter_SSs
Peter_SSs
J
  • Question Question
Removing blank rows based on cell value
Replies
2
Views
256
Peter_SSs
Peter_SSs
V
  • Question Question
Daily datewise calculation with colour formatting
Replies
7
Views
230
Cubist
Cubist
Dan Wilson
  • Solved
Formula help.
2
Replies
10
Views
221
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

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