Hi there, I would really appreciate some help. I am working in excel 2010.
My Data is in Columns as below (this is a snapshot only, my spreadsheet is variable in size, but normally about 250 columns):
C1 C2 C3 C4 C5
Peer 7.0 6.0 6.0 6.0
Peer 7.0 6.0 7.0 7.0
Peer 1.0 6.0 5.0 -
Manager 7.0 7.0 7.0 7.0
Peer 7.0 6.0 6.0 6.0
Peer 6.0 6.0 5.0 6.0
Peer 6.0 6.0 6.0 -
Manager 7.0 6.0 4.0 5.0
Report 6.0 6.0 7.0 6.0
I need to average the data in C2 - C5 using the value in C1 as the criteria. So for instance, an average of all manager ratings from Row 4 and 8 (ie. answer 6.3)
I tried formula [=ROUND(AVERAGEIF(R2C5:R[-9]C5,"MANAGER",R2C:R[-9]C[8]),1)]. However, this returns an average from C2 only.
I can write a loooong formula to fix my problem but I would like to avoid this if possible. I can't use an array formula as I am using a data manipulation program that writes these formulas in to excel for me.
My only other option is to restructure my spreadsheet to avoid this. Before I start down this path, does any one have any suggestions?
Many thanks in advance.
ST
Bookmarks