| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only Cells with positive answer to IF formula
I have an IF formula in Column A that puts a number from another cell if
positive or "" if negative. I want to put in say column B a formula that will bring all the positive responses in order to the top of Column B, instead of just spread out in A, is this possible or is their another solution Regards David |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Only Cells with positive answer to IF formula
=IF(ROWS($1:1)<=COUNTA($A$1:$A$500),INDEX($A$1:$A$ 500,SMALL(IF($A$1:$A$500<"",ROW($A$1:$A$500)-MIN(ROW($A$1:$A$500))+1),ROWS($1:1))),"")
This is an array formula that must be inserted with: CNTRL-SHFT-ENTER rather than the ENTER key (from a Ron Coderre post) -- Gary''s Student - gsnu200803 "Davidm" wrote: I have an IF formula in Column A that puts a number from another cell if positive or "" if negative. I want to put in say column B a formula that will bring all the positive responses in order to the top of Column B, instead of just spread out in A, is this possible or is their another solution Regards David |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Only Cells with positive answer to IF formula
This works well is their anyway of having it so #NUM! doesnt appear in cells
after the numbers are in order Thanks David "Gary''s Student" wrote: =IF(ROWS($1:1)<=COUNTA($A$1:$A$500),INDEX($A$1:$A$ 500,SMALL(IF($A$1:$A$500<"",ROW($A$1:$A$500)-MIN(ROW($A$1:$A$500))+1),ROWS($1:1))),"") This is an array formula that must be inserted with: CNTRL-SHFT-ENTER rather than the ENTER key (from a Ron Coderre post) -- Gary''s Student - gsnu200803 "Davidm" wrote: I have an IF formula in Column A that puts a number from another cell if positive or "" if negative. I want to put in say column B a formula that will bring all the positive responses in order to the top of Column B, instead of just spread out in A, is this possible or is their another solution Regards David |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Only Cells with positive answer to IF formula
Su
=IF(ISERROR(originalformula),"",originalformula) -- Gary''s Student - gsnu200803 "Davidm" wrote: This works well is their anyway of having it so #NUM! doesnt appear in cells after the numbers are in order Thanks David "Gary''s Student" wrote: =IF(ROWS($1:1)<=COUNTA($A$1:$A$500),INDEX($A$1:$A$ 500,SMALL(IF($A$1:$A$500<"",ROW($A$1:$A$500)-MIN(ROW($A$1:$A$500))+1),ROWS($1:1))),"") This is an array formula that must be inserted with: CNTRL-SHFT-ENTER rather than the ENTER key (from a Ron Coderre post) -- Gary''s Student - gsnu200803 "Davidm" wrote: I have an IF formula in Column A that puts a number from another cell if positive or "" if negative. I want to put in say column B a formula that will bring all the positive responses in order to the top of Column B, instead of just spread out in A, is this possible or is their another solution Regards David |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| rounding down to positive when adding cells | Excel Worksheet Functions | |||
| blank cells in a formula gives me an answer not acceptable to prin | New Users to Excel | |||
| Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) | |||
| formula to copy last positive number in range of cells | Excel Worksheet Functions | |||
| how can i create a formula or format the cells so the answer is a. | Excel Discussion (Misc queries) | |||
Linear Mode
