[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1517
  • Last Modified:

Binned average using excel. THIS IS URGENT!

          I need this data within a few hours if possible! I have uploaded the data. Column C & D are observed vs. forecast data. I want the binned average for data in columns C in column E and I the same thing for column D in column F. I pulled up a formula for binned averages and placed it in E1 & F1 but nothing seems to happen. I tried copying down the lengthe of the column and still nothing. Help! Thanks.
binnedso4konascatter12.xls
0
libertyforall2
Asked:
libertyforall2
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
jimyXCommented:
You need to put "=" at the beginning of each formula plus name one of the cell as "bin" so it holds the bin size.

steps:
click cell N1 then go to the Name Box and type BIN
Then update your formulas by putting = at the beginning
Provide bin number in cell N!

Check the attachment:
binnedso4konascatter12-updated.xls
0
 
barry houdiniCommented:
Probably better to do with OFFSET than INDIRECT, i.e. in E1

=IF(MOD(ROW(),bin)=1,AVERAGE(OFFSET(C1,,,bin+1)),"")

regards, barry
0
 
jimyXCommented:
The other Rows are not showing data because of "=1":
=IF(MOD(ROW(),bin)=1,AVERAGE(INDIRECT("C"&ROW()&":C"&bin+ROW())),"")

To get it to work for all the rows make it "=ROW()":
=IF(MOD(ROW(),bin)=ROW(),AVERAGE(INDIRECT("C"&ROW()&":C"&bin+ROW())),"")

Updated sheet attached.
binnedso4konascatter12-updated-2.xls
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
barry houdiniCommented:
Jimy, if you use that version then when bin = 7 then the ROW will only be the same as MOD for rows  1 to 6 and you won't get anything further down

The first version would give you a figure on every BINth row - perhaps libertyforall2 could clarify what the result should look like - given the proposed formulas I assume there should be a result every BINth row......but the initial formulas would average BIN+1 rows so I'd propose a change to

=IF(MOD(ROW(),bin)=1,AVERAGE(OFFSET(C1,,,bin)),"")

If BIN value doesn't need to be variable then you could achieve the same thing by using a simple AVERAGE function in row 1 and then copying that formula along with the requisite number of blank rows....

see attached where, assuming BIN is 5 I used this formula in E1 copied to F1 and then down

In columns G and H I achiebed the same by putting this formula in G1

=AVERAGE(C1:C5)

and copying to H1

....then selecting the whole block G1:H5 and copying that block down

regards, barry
26891906.xls
0
 
libertyforall2Author Commented:
Works great.
0
 
Nikhil SharmaCommented:
can some one help me in doing average of a large set of data similar to binned average. but bin size is variable every time. for example : 1 ,1, 1,2,2,2,2,2,3,3,4,4,4,4,5,6,6,6,6.
0
 
Nikhil SharmaCommented:
0
 
Nikhil SharmaCommented:
can some one help me in doing average of a large set of data similar to binned average. but bin size is variable every time.

please find the attached file
average.xlsx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now