Help with formula...

[Guest]

Each cells R54:R82 has a range of number in it. Column S, T, and U are
updated everyday. This is an example of rows 54, 55, and 56:

R54,S54,T54,U54

0-.4, 1, 0, 1.5
..5-.9, 0, 0, 0
1.0-1.5, 2, 1, 1.3

I would like to setup a formula in another column that will pick our the
rows that have a number greater than 0 in either column S, T, or U.

So in the above example I would get this:
0-.4, 1, 0, 1.5
1.0-1.5, 2, 1, 1.3

Thank you

 

[Domenic]

Try the following...

W54:

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(S54:U82,ROW(S54:U82)-ROW(S54),0,1))>0))

X54, copied across and down:

=IF(ROWS(X$54:X54)<=$W$54,INDEX(R$54:R$82,SMALL(IF(SUBTOTAL(9,OFFSET($S$5
4:$U$82,ROW($S$54:$U$82)-ROW($S$54),0,1)),ROW($R$54:$R$82)-ROW($R$54)+1),
ROWS(X$54:X54))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Guest]

nevermind, I found my error, thanks

Try the following...

W54:

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(S54:U82,ROW(S54:U82)-ROW(S54),0,1))>0))

X54, copied across and down:

=IF(ROWS(X$54:X54)<=$W$54,INDEX(R$54:R$82,SMALL(IF(SUBTOTAL(9,OFFSET($S$5
4:$U$82,ROW($S$54:$U$82)-ROW($S$54),0,1)),ROW($R$54:$R$82)-ROW($R$54)+1),
ROWS(X$54:X54))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Guest]

Domenic,
That works for cell x54,y54,z54, and AA54 but when I copy it down I get
#NUM! Any idea why? Thanks for the help.

 

[Guest]

Domenic,
Actually there is a little problem. W54 = 8 when that formula is entered.
In some rows column S is 0 but column T is not. I need W54 to take into
account column T also. Any ideas? Thanks

 

[Guest]

Domenic,
I figured out my problem. It probably isn't the best way to make it work
but it does the job. Thanks for you help.

 

[Domenic]

Domenic,
I figured out my problem. It probably isn't the best way to make it work
but it does the job.

If you'd like, you can tell me what the problem is and I'll see if I can
change/modify the formula.

 

[Guest]

Here is the problem: W54 = 8 when that formula is entered.
In some rows column S is 0 but column T is not. I need W54 to take into
account column T also. Any ideas? Thanks

 

[Domenic]

Here is the problem: W54 = 8 when that formula is entered.
In some rows column S is 0 but column T is not. I need W54 to take into
account column T also. Any ideas? Thanks

Actually, the formula I offered will take into account Column S, T, and
U, as you requested. Can you post the exact formula that you're using?

 

[Guest]

Here are columns R, S, T, and U (rows 54 to 82).
0-.4 2 1 0.95
..5-.9 1 2 -1.26
1.0-1.4 0 0 0.00
+1.5 0 1 -1.40
-1 RL 0 1 -1.00
RL 0 0 0.00
ARL 0 0 0.00
+-200 0 0 0.00
-190/-199 0 0 0.00
-180/-189 0 0 0.00
-170/-179 0 1 -1.76
-160/-169 0 0 0.00
-150/-159 0 0 0.00
-140/-149 0 1 -1.40
-130/-139 0 0 0.00
-120/-129 0 0 0.00
-110/-119 0 1 -1.12
-101/-109 1 0 1.00
100/109 1 0 1.07
110/119 0 1 -1.00
120/129 0 0 0.00
130/139 0 0 0.00
140/149 0 0 0.00
150/159 1 0 1.50
160/169 0 0 0.00
170/179 0 0 0.00
180/189 0 0 0.00
190/199 0 0 0.00
+200 0 0 0.00

I put
=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(S54:U82,ROW(S54:U82)-ROW(S54),0,1))>0)) into
W54. It gives me 5 (I assume it is getting 5 from column S). It should be
11.

I put
=IF(ROWS(X$54:X54)<=$W$54,INDEX(R$54:R$82,SMALL(IF(SUBTOTAL(9,OFFSET($S$54:$U$82,ROW($S$54:$U$82)-ROW($S$54),0,1)),ROW($R$54:$R$82)-ROW($R$54)+1),ROWS(X$54:X54))),"") into X54 and copied it across and then down. This is what I get:

0-.4 2 1 0.95
..5-.9 1 2 -1.26
+1.5 0 1 -1.4
-170/-179 0 1 -1.76
-140/-149 0 1 -1.4

you can see it is skipping some rows it shouldn't be (like -1 RL). I am
using ctrl,shift,enter when applying the fomula in X54, Y54, Z54, and AA54.

Any idea what is wrong? Thanks for the help.

 

[Domenic]

Since your data includes negative numbers, try the following instead...

W54:

=SUMPRODUCT(--(MMULT(--(S54:U82<>0),{1;1;1})>0))

....confirmed with just ENTER

X54, copied across and down:

=IF(ROWS(X$54:X54)<=$W$54,INDEX(R$54:R$82,SMALL(IF(MMULT(--($S$54:$U$82<>
0),{1;1;1})>0,ROW($R$54:$R$82)-ROW($R$54)+1),ROWS(X$54:X54))),"")

....confirmed with CONTROL+SHIFT+ENTER

Hope this helps!

 

[Guest]

perfect, thank you very much.

Since your data includes negative numbers, try the following instead...

W54:

=SUMPRODUCT(--(MMULT(--(S54:U82<>0),{1;1;1})>0))

....confirmed with just ENTER

X54, copied across and down:

=IF(ROWS(X$54:X54)<=$W$54,INDEX(R$54:R$82,SMALL(IF(MMULT(--($S$54:$U$82<>
0),{1;1;1})>0,ROW($R$54:$R$82)-ROW($R$54)+1),ROWS(X$54:X54))),"")

....confirmed with CONTROL+SHIFT+ENTER

Hope this helps!