Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 12th 05, 05:56 PM posted to microsoft.public.excel.worksheet.functions
JR
 
Posts: n/a
Default cell reference math

I have a spreadsheet that is filled by experimental data. The number of data
points (rows) can be 300-6000. I am tasked to take 50 point averages at the
25% and 75% points of the data. I can get the number of data points using
using the COUNTIF function. I need to be able to adjust the AVERAGE function
based on the COUNTIF results. Is there a way to set the cell reference based
upon this result? Are there any other ideas as to how to solve this? If my
problem is not clear, please ask questions.

  #2   Report Post  
Old December 12th 05, 06:07 PM posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default cell reference math

JR,

How about these, for data in column A, and a title value in cell A1

=AVERAGE(OFFSET(A1,COUNT(A:A)*0.25-25,0,50,1))
and
=AVERAGE(OFFSET(A1,COUNT(A:A)*0.75-25,0,50,1))

HTH,
Bernie
MS Excel MVP


"JR" wrote in message
...
I have a spreadsheet that is filled by experimental data. The number of data
points (rows) can be 300-6000. I am tasked to take 50 point averages at the
25% and 75% points of the data. I can get the number of data points using
using the COUNTIF function. I need to be able to adjust the AVERAGE function
based on the COUNTIF results. Is there a way to set the cell reference based
upon this result? Are there any other ideas as to how to solve this? If my
problem is not clear, please ask questions.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to include a cell reference that is contained in a cell withi. dutchinny Excel Worksheet Functions 5 October 24th 05 01:07 AM
How do I leave formula cell blank if 2nd reference cell is empty? Liana S Excel Discussion (Misc queries) 2 October 21st 05 04:38 PM
copied formula has correct cell reference, but result of original lvito Excel Worksheet Functions 1 October 14th 05 04:37 PM
Problem with formulas changing cell reference janicesweet Excel Discussion (Misc queries) 1 August 2nd 05 06:23 PM
Flexible Cell Reference Brandt Excel Discussion (Misc queries) 5 June 2nd 05 10:23 PM


All times are GMT +1. The time now is 07:14 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017