Hiding formula to calculate % visits met

[Michele]

Hi,

I am using excel 2000 and am not sure how to ask this question. We do
hospital vists monthly and have a quota each month to meet. For example:

Hosp Frequency May June July
1 biweekly 1, 15, 27 4, 19 7
2 quarterly 12

There is a whole list of hospitals. I want to insert a formula in the month
cells to see if the frequency is met each month (%) by having it calculated
automatically as data is entered. So I am assuming I have to hide a number
under the biweekly word, i.e. 2, to figure out a formula in each month cell
and somehow convert the day of the month to = 1 each time a date is entered
in the month. I would need to do this quarterly, annually and daily
depending on the requirement for each hospital. Does anyone know if this can
be done and how to do it? We want to retain the dates of the visits. If more
clarification is needed on what I am asking feel free to email me. I am not
sure I am explaining this clearly. Also, is there a way to change the color
of the cell automatically when the quota is met? Thanks so much.

 

[Wanda]

Hi michele!

I'll tackle the one I know on changing the color of the cell when quota is
met. To do so, choose "Conditional Formating" from the Format menu. You can
enter a formula or value for the cell, then format how you want the cell to
appear when the condition is met. You can create up to at least 3 conditions
(that I know of - could be more).

I do know that you can put formulas in cells, then hide the columns or rows
that the have the formulas in them.

 

[Bernard Liengme]

It would help to know exactly how the data is stored. Do you really have
1,15,27 in ONE cell?

It is easy to see that May and June for the biweekly are ok, since there are
at least two dates (and hence one or more commas) in each cell. What about
July? Do you want to have the formula check that that month is not yet
finished?

Quarterly: Can we assume we start the count in January? So we need one entry
(or more) in every three month range.

The second part is easy (once the first part is solved), we can use
Conditional Formatting to change a cell colour based on the result of the
new formula.

best wishes

 

[Michele]

Yes, Bernard to the first question. The directors put the exact date of the
month that they make the visit, in one cell.

Yes, it would be nice if the formula checked that the month has not met the
goal yet.

Also, yes, quarterly would start in January so reports are made
quarterly(jan-mar; april-june; july-sept & oct-dec).

Any ideas, or should we approach how we keep track a different way?

Thank for the info on conditional formatting. That should work once I have
the criteria done.

Michele

 

[Michele]

Thanks Wanda, I did not know about conditional formatting, so that will work.
Michele