If goal not met 2 out of previous 6 days

  • Thread starter Thread starter Steve Mc
  • Start date Start date
S

Steve Mc

I need to track if a specific goal is not met. I would
like to have an ALERT show in an empty cell if the goal
(B2) is not achieved in any 2 of the prior 6 days.
E.g.

A B
Goal-> 250,000
SAT 240,000
SUN 230,000
MON 270,000
TUE 273,000
WED 260,000
THU 245,000 After thurs data entered = ALERT
FRI 272,000 After fri data entered = ALERT
SAT 479,000 After sat data entered = No alert
SUN 139,000 After sun data entered = ALERT
MON 662,000 After mon data entered = ALERT
TUE 260,000 After tue data entered = ALERT
WED 321,000 After wed data entered = No alert
THU 280,000 After thu data entered = No alert
FRI 200,000 After fri data entered = ALERT
etc.

Thanks,
 
Try:

=IF(COUNT(B3:B100)<6,"",IF(SUM(--(OFFSET(B3,COUNT(B3:B100)-
1,,-6)<$B$2))>=2,"ALERT",""))

Array-entered (ie press ctrl/shift/enter).

Extend the range beyond B100 in the formula if necessary.

HTH
Jason
Atlanta, GA
 
Try:

=IF(COUNT(B3:B100)<6,"",IF(SUM(--(OFFSET(B3,COUNT(B3:B100)-
1,,-6)<$B$2))>=2,"ALERT",""))

Array-entered (ie press ctrl/shift/enter).
Click to expand...

You really must hate SUMPRODUCT/love array formulas. But it's clever to use the
undocumented feature of negative 4th (and available, but not used here, 5th)
argument(s) to OFFSET. Debatable whether

OFFSET(B3,COUNT(B3:B100)-1,,-6)

gains you anything in efficiency to justify the reduction in readability and
maintainability compared to using

OFFSET(B3,COUNT(B3:B100)-6,,6)

Now, as for the OP's problem, if the OP's sample data were laid out with the
goal in cell B2, with the first SAT in A3 and the corresponding 240,000 in B3,
and sequential rows below containing the data shown, and if the results should
appear in column C in the same row as the corresponding last day of any 6 day
periods, then the first formula would be needed in C8, and it could be simply

=IF(COUNTIF(B3:B8,"<"&$B$2)>1,"ALERT","")

then C8 filled down as far as needed.
 
Thanks guys,
I was able to get the countif solution to work, but the
offset one produced alerts on all the c column cells. I
may not have correctly entered it, as I'm not familiar
with offset. However, in re-reading my original question,
I realized I wasn't very clear regarding the alert. I was
hoping to be able to have that alert prompt/show in one
specific cell, such as D1, if the last entry in the B
column produced an alert in it's adjacent C cell. Sort of
like, after data is entered in b15, if c15 has an alert,
then show that alert on D1. If after data is entered in
b16, and c16 doesn't have an alert, then don't show the
alert in D1 ( even though c15 still has that alert). Maybe
this is too much, or I'm not explaining it well enough,
but any help would be very appreciated.
Thanks,
 
Harlan can you please explain to me what the undocumented feature of
negative 4th is. Thank you very much!
 
Harlan can you please explain to me what the undocumented feature of
negative 4th is. Thank you very much!
Click to expand...

From online Help:

Syntax

OFFSET(reference,rows, cols,height,width)

...

Height is the height, in number of rows, that you want the returned reference
to be. Height must be a positive number.

Width is the width, in number of columns, that you want the returned reference
to be. Width must be a positive number.


Height is the 4th argument, and Width is the 5th argument. Online Help is clear
that these *should* be positive, but Excel accepts negative arguments. The
difference is that positive Height arguments go downward from the top-left cell
given by OFFSET(Reference,rows,columns), and negative Height arguments go
upward. I actually didn't know about this functionality until I tested Jason's
formula. I guess the only 'Chinese Walls' in Microsoft are between programmers
and user documentation writers.
 
Back
Top