How to store the calculated value into the original cell?
 

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

How to store the calculated value into the original cell?
 

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
--
HTH...

Jim Thomlinson


"scw_mlc" wrote:

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