PDA

View Full Version : Solved: Help with Dynamic formula



Lawrence
10-14-2008, 11:50 AM
I have a formula that looks at the last 4 weeks (AY12) and returns the numbers of red ratings. However, as data gets added, I manually move the range for about 200 rows.

I am looking for a way to have it look up the last column filled in, then count the last 4 weeks from that column, and return the number of red ratings.

Something similar to the formula in cell AT12 (Thank you Bob for that) but looking at 4 columns instead of a single cell to capture the right data as people fill in the weeks, and I can't figure it out.

Bob Phillips
10-14-2008, 01:47 PM
Try this array formula

=SUM(OFFSET($I12,0,MAX(IF($I12:$AS12<>"",COLUMN($I12:$AS12)-COLUMN($I12)+1))-4,1,4))

holshy
10-14-2008, 01:52 PM
Try this array formula

=SUM(OFFSET($I12,0,MAX(IF($I12:$AS12<>"",COLUMN($I12:$AS12)-COLUMN($I12)+1))-4,1,4))
That's much more elegant than what I was working on, but I don't think it does exactly what he asked.

minor tweak, still an array
=SUM(IF(OFFSET($I12,0,MAX(IF($I12:$AS12<>"",COLUMN($I12:$AS12)-COLUMN($I12)+1))-4,1,4)<$BF$12,1,0))

Bob Phillips
10-14-2008, 02:28 PM
If you are right, you probably are, I think you mean BD12 not BF12, and you can simplify it more

=SUM(--(OFFSET($I12,0,MAX(IF($I12:$AS12<>"",COLUMN($I12:$AS12)-COLUMN($I12)+1))-4,1,4)<$BD$12))

Lawrence
10-14-2008, 04:04 PM
Thank you both, it works. :)

EDIT: How can I modify it to make sure it is <BD12 but also >0?

Bob Phillips
10-15-2008, 12:59 AM
=SUM((OFFSET($I12,0,MAX(IF($I12:$AS12<>"",COLUMN($I12:$AS12)-COLUMN($I12)+1))-4,1,4)<$BD$12)*(OFFSET($I12,0,MAX(IF($I12:$AS12<>"",COLUMN($I12:$AS12)-COLUMN($I12)+1))-4,1,4)>0))