|
Help with formula...
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 |
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! In article , Jambruins wrote: 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 |
nevermind, I found my error, thanks
"Domenic" wrote: 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! In article , Jambruins wrote: 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,
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. "Domenic" wrote: 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! In article , Jambruins wrote: 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,
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 "Jambruins" wrote: 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,
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. "Jambruins" wrote: 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 "Jambruins" wrote: 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 |
In article ,
Jambruins wrote: 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. |
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" wrote: In article , Jambruins wrote: 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. |
In article ,
Jambruins wrote: 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? |
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. |
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! In article , Jambruins wrote: 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. |
perfect, thank you very much.
"Domenic" wrote: 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! In article , Jambruins wrote: 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. |
| All times are GMT +1. The time now is 04:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com