Excel average formula using multiple criteria

[medic133]

I have a spreadsheet in which I keep track of student's performances in several evaluation points. Row 1 of the spreadsheet is dates (beginning with column C), column A of all subsequent rows describes the evaluation point. The remaining cells in the worksheet is where I enter values (1 through 5) for the given date and evaluation point indicating the student's performance for that given date. My question is this: I need a formula for column B which will average the numbers in the row providing two criteria are met: within a given date range and not an empty cell (some students won't be evaluated on a given point on a specific date). I think I need to use the SUMPRODUCT function which will take advantage of the numerical equivalents of the Boolean functions as well as the COUNTIF function, but I can't seem to get it to work. Any suggestions would be greatly appreciated. If you could also briefly explain why your suggestion would work, that would be helpful so that I might apply it elsewhere under different circumstances.

Another simple question: I've noticed in several of these forums people using the notation $A$1. What is with the dollar signs?

Thanks much in advance,

Phil

 

[gizmo1973]

You could use =average with an if statement on the date range.
the dollar sign basically "locks" the data to that cell if wrapped in dollars or if in front of the row locks the row, a correct name for this escapes me as it's late friday afternoon but I'm sure someone will post the correct term.

Regards, Phil.

Full Member of Shareholders United.
Show your true support here:

http://www.shareholdersunited.org/join.php

"M.U.F.C. Not for sale to Glazer"

 

[xlbo]

Gizmo - Absolute vs Relative referencing

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[gizmo1973]

See!

Regards, Phil.

Full Member of Shareholders United.
Show your true support here:

http://www.shareholdersunited.org/join.php

"M.U.F.C. Not for sale to Glazer"

 

[xlbo]

To clarify - I think you probably do need SUMPRODUCT AND COUNTIF - I don't think a simple IF will do

Absolute referencing describes a method of keeping a column or row reference still when a formula is moved

lets say =B10 is the formula in a cell

If you drag that down 1 row, it becomes =B11

This is relative referencing

if the formula is =B$10 and you drag down, it stays the same

This is partial absolute referencing

If you drag to the right, it will become =C$10 so...

change to =$B$10 and wherever you drag it, the formula will remain the same

This is Full absolute referencing

You can also put the dollar in front of the column (and not the row)

I'm having difficulty pictureing your data though - can you describe it again or post an example of the layout ??

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[Loomah]

Hi
This is a bit of a beast and I'd like to think that someone could come up with something a little less cumbersome but...

=(SUMPRODUCT(IF(dates<=StartDate,1,0)*IF(dates>=EndDate,1,0)*IF($C4:$N4>0,$C4:$N4,0)))/SUMPRODUCT(IF(dates<=StartDate,1,0)*IF(dates>=EndDate,1,0)*IF($C4:$N4<>"",1,0))

Entered as an array formula would do the trick

'dates' is a named range containing (go on, guess) the dates
C4:N4 is the range containing the data. Note that it must have the same number of columns as 'dates'
'StartDate' & 'EndDate' are named ranges containing the date range for your criteria.

As I say I'd like there to be an easier solution and I'll be looking out for one!!

Happy Friday


;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[GrayShades]

I am wondering if your denominator will produce some zeros, hence not accetpable.

Since you only want the count in the denominator, why not use this (whihc does not count the blank cells):

COUNTIF($C$4:$N$4,">0")

Software: XL2002 on Win2K
Humanware: Older than dirt

 

[GrayShades]

Oops, I had only half right... Sorry.

There will be some zeroes, but my soultion only counted values, not whether they were within the date range.

Software: XL2002 on Win2K
Humanware: Older than dirt