This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I working on a balanced scorecard which looks like this:
| (A)Month | (B)Enddate | (C)Meeting date | (D)Score |
|---|---|---|---|
| Jan | 31-1-2010 | 20-1-2010 | 1 |
| Feb | 28-2-2010 | 0 | |
| Mrt | 31-3-2010 | 25-3-2010 | 1 |
| April | 30-4-2010 | 15-4-2010 | 1 |
| May | 31-5-2010 | 0 |
I want to calculate an average score (last column) based on today's date (07-05-2010).
The score per row is calculated by a formula: =IF(AND(C2>0;C2<=B2);1;0)
The formula I have created for calculating the average: =AVERAGEIF(B2:B6;"<=Today()";D2:D6).
The formula results in a #DIV/0! and I expected it to show 0,75.
From the looks of it the evaluation of the dates in the B-column versus the today() function does not lead to any result.
According to Excel Help is should be possible to enter a function in the criteria field and consequently I do not understand why the formula does not display the expected result.
Is there a logical explanation for this?
Regards, Werner
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
=AVERAGEIF(B2:B6;"<="&TODAY();D2:D6)
Your formula was looking for cells in column B containing string values less than or equal to the string "Today()"
Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
Although you didn't ask for this, you could simplify your
=IF(AND(C2>0;C2<=B2);1;0)
to
=(C2>0)*(C2<=B2)
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"Werner_w69" wrote in message news:0e2be1f0-98f4-4aef-ad39-c736b4d81592...
I working on a balanced scorecard which looks like this:
(A)Month (B)Enddate (C)Meeting date (D)Score Jan 31-1-2010 20-1-2010 1 Feb 28-2-2010 0 Mrt 31-3-2010 25-3-2010 1 April 30-4-2010 15-4-2010 1 May 31-5-2010 0
I want to calculate an average score (last column) based on today's date (07-05-2010).
The score per row is calculated by a formula: =IF(AND(C2>0;C2<=B2);1;0)
The formula I have created for calculating the average: =AVERAGEIF(B2:B6;"<=Today()";D2:D6).
The formula results in a #DIV/0! and I expected it to show 0,75.
From the looks of it the evaluation of the dates in the B-column versus the today() function does not lead to any result.
According to Excel Help is should be possible to enter a function in the criteria field and consequently I do not understand why the formula does not display the expected result.
Is there a logical explanation for this?
Regards, Werner
Cheers, Shane Devenshire
Bill, thank you very much for cracking this case!