Dynamic Counting
Posted by Christine on May 10, 2001 9:20 AM
Hi all..
I am new to excel, so I apologize if this is an easy answer.
What I am doing in my project is averaging a bunch of numbers
and dumping the answer onto another sheet. I am using
an array formula to do an average if.
The problem here is that I dont want to have to update the
range for excel to average from (ie D2:D6), My data my
constantly be changing and the number of numbers to be
averaged could be from D2:D300 one day to D2:D600 the next.
What I would like to be able to do, is count the number of
of numbers to average(#rows in my file) in my file(without
knowing the range)and use that number for all of my
formulas for the range.
(ie:
AVERAGE(IF(Page1!$A$2:$A$constantvalue=somecriteria,
Page1!$D$2:$D$constantvalue, ""))
)
was that confusing?? :)
THanks!
christine
a
Posted by Dave Hawley on May 10, 2001 9:24 AM
Hi Christine
If you follow my link to my website you will see alink to "Dynamic Ranges" follow this and you will see what you are after.
Dave
OzGrid Business Applications
Posted by Dave Hawley on May 10, 2001 9:28 AM
Christine, I notice you say you are new to Excel. I also note you are using array formulas. I have some information on my Website also about arrays and how they can become a major problem. Consider using the Database functions (DAVERAGE) instead. They are designed for the job and wont cause any of the problems that arrays can.
Dave
OzGrid Business Applications
Posted by Christine on May 10, 2001 9:41 AM
I was having trouble getting the areas defined
thats why i wasnt using it..
in my project I do something similar to this:
A B
1 work# %finished
2 A32 50%
3 B23 30%
4 A32 20%
5 C25 10%
and what I have to do is take the average of how
much the project is finished according to each
work#(number) So I average all of the A32's the B23's
and so on separately..
So, if anyone has any suggestions with that info, I will take it
too! THANKS!
I will take a look at those sites!
Christine
Posted by christine on May 10, 2001 9:58 AM
Can anyone provide me with a link that better explains
the use of DAVERAGE?? and the other database
functions?
Posted by Dave Hawley on May 10, 2001 10:11 AM
Christine
Try this. I'll assume your "Work#" heading is in A1 and "%Finished" is in B1.
1. Create a Dynamic range for "%Finished" eg:
=OFFSET($B$2,0,0,COUNTA($B$2:$B$5000),1)
Call it "PerFinished"
2. Create a Dynamic range for "Work#" eg:
=OFFSET($A$2,0,0,COUNTA($A$2:$A$5000),1)
Call it "WorkNo"
3. Create a Dynamic range for the whole table eg:
=OFFSET($A$1,0,0,COUNTA($A$1:$A$5000),2)
Call it "Thelot"
4. Copy the headings in A1:B1 to E1:F1
5. Select E2 and go to Data>Validation
and select "list" then in the "Source" box type:
=WorkNo
6. Select F2 and go to Data>Validation
and select "list" then in the "Source" box type:
=PerFinished
7 Name Range E1:F2 Criteria.
8 Now in any cell put:
=DAVERAGE(TheLot,F1,Criteria)
Change the Critria to Average by selecting a entry from the list in Cell E2. You can of course use the same method with any of the Database functions.
DaveOzGrid Business Applications
Posted by Dave Hawley on May 10, 2001 10:24 AM
Hi Christine
Type: DAVERAGE into the Excel help, there is quite a good amount of information there.
I will look about for a link for you.
Dave
OzGrid Business Applications
Posted by Dave Hawley on May 10, 2001 10:51 AM
Christine, here is one. Hope it helps. Just yell if unsure.
http://www.erlandsendata.no/english/functions/lookup/dbfunctions.htm
DaveOzGrid Business Applications
