How to store the calculated value into the original cell?

[scw_mlc]

Hi All:

I'm workng on an issue with MS-Excel. I have two column to store the
training data of each staffs. The first column stores my staff's name, and
the second column stores the training status either "Valid" or "Expired". I
need to get the quarterly percentage of how many staffs on "Expired" training
status.

My scenario is shown below:
Example:

Current system date - 2007-02-21 (i.e. =now())

A B C
Name Date Quailifed Expiry Status
1 Peter 2007-01-31 Valid
2 Judy 2005-02-11 Expired
3 Rocky 2006-01-12 Expired

Total of Staff - COUNT(A1:A3) -> 3 (e.g. G1)
Total of Expired - COUNTIF(C1:C3,"Expired") -> 2 (e.g. H1)

1st Quarter - IF(MONTH(NOW())=3,1 - (H1/G1))
2nd Quarter - IF (MONTH(NOW())=6,1 - (H1/G1))
3rd Quarter - IF (MONTH(NOW())=9,1 - (H1/G1))
4th Quarter - IF (MONTH(NOW())=12,1 - (H1/G1))

My problem is if the value of month changes from 3 to 6, I cannot retain the
number of expired value because the calculation of H1/G1 will also change the
value.

I want to store the calculated value of H1/G1 in order to present the
quarterly data on the report. Would you please advise how to resolve this
issue? Your help and efforts are highly appreciated.

Thank you,

Alan

 

[Jim Thomlinson]

So if I understand you correctly you wnat to know what % of Status values Are
Expired or Valid for a given quarter... that can be done with pivot talbes of
with sumproduct formulas. Here is the sumproduct formulas...

=SUMPRODUCT(--(MONTH($B$2:$B$4)>=1), --(MONTH($B$2:$B$4)<=3),
--($C$2:$C$4="Expired"))/SUMPRODUCT(--(MONTH($B$2:$B$4)>=1),
--(MONTH($B$2:$B$4)<=3))

This will determine the % of Expired status's for quarter 1 as a percentage
of all statuses for quarter 1. This assumes dates are in column B and
statuses are in column C. Here is a link to the sumproduct formula to give
you an idea what is going on...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html