Formula for returning only positive values from a range

**Novosedoff** · 12-28-2014, 10:00 AM

There was an example of formula that returns only positive values from a range in John Walkenbach old book Microsoft Bible 2003.
(Chapter 15, Working with Multicell Array Formulas). In case all data are stored in column range Data the formula takes the following view:
=IF(ISERR(SMALL(IF(Data>0;ROW(INDIRECT("1:"&ROWS(Data))));ROW(INDIRECT("1:"&ROWS(Data)))));"";INDEX(Data;SMALL(IF(Data>0;ROW(INDIRECT("1:"&ROWS(Data))));ROW(INDIRECT("1:"&ROWS(Data))))))

My question is what I need to change in the above formula if Data is not a column range, but a row range?

I tried to change all ROW to COLUMN and all ROWS to COLUMNS, but that only caused N/A to show up in all cells..

**zbor** · 12-28-2014, 10:08 AM

Try this
confirmed with ctrl+shift+enter (not just enter)

Formula:

Please Login or Register  to view this content.

Doesn't matter is your range in row or column.

**Novosedoff** · 12-28-2014, 10:24 AM

Thanks. Is it possible to change somehow the formula so it allows the Data range to be entered explicitly? For instance, by typing C65:J65 instead of Data (I guess ROWS(A$1:A1) must be then changed to ROWS(C$65:C65)?).

I ask this because my attempt to do so has failed...

**Glenn Kennedy** · 12-28-2014, 10:29 AM

Leave rows the way it was. it is a simple counter, and needs to start at 1

**Tony Valko** · 12-28-2014, 10:42 AM

See this...

Lookup with multiple instances of the lookup value

**Novosedoff** · 12-28-2014, 11:09 AM

Originally Posted by Glenn Kennedy

Leave rows the way it was. it is a simple counter, and needs to start at 1

It just doesnt work the way I wanted it to. In John WalkenBah's example one can squeeze his formula in whatever column one likes, just by making sure the size of the new selection matches the size of the original range. Is it possible to do the same with the rows?

**Novosedoff** · 12-28-2014, 11:12 AM

Originally Posted by Tony Valko

See this...

Lookup with multiple instances of the lookup value

Sorry, perhaps I am a little slow. Can you make it a little more obvious for me? What am I supposed to do with the example that does "smth else"?

**zbor** · 12-28-2014, 11:26 AM

Just change data with desired range:

Formula:

Please Login or Register  to view this content.

**zbor** · 12-28-2014, 11:26 AM

Just change data with desired range:

Formula:

Please Login or Register  to view this content.

**Glenn Kennedy** · 12-28-2014, 11:27 AM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**Novosedoff** · 12-28-2014, 11:31 AM

Originally Posted by Glenn Kennedy

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Here it goes, buddy.

In red are highlighted the original cells. One has to choose only those that are >0. They must be copied into any of yellow rows.

**Novosedoff** · 12-28-2014, 11:37 AM

Originally Posted by zbor

Just change data with desired range:

Formula:

Please Login or Register  to view this content.

By some reason when I use your formula Excel fills all the resultant cells with the same value, i.e. the first non-zero value from the original row. This is wrong.

**zbor** · 12-28-2014, 11:39 AM

Did you confirmed with ctrl+shift+enter (not just enter)?

See above attachment where it works.

**FDibbins** · 12-28-2014, 11:41 AM

The suggested formula is an ARRAY formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**martindwilson** · 12-28-2014, 11:45 AM

if your datarange is say in d1:m1
or it could be in a1:10
name the chosen range i used data1
if you want to extract the lowest non 0
to cells filled filled across
=IF(ISERR(SMALL(IF(data1>0,data1),COLUMNS($A:A))),"",SMALL(IF(data1>0,data1),COLUMNS($A:A)))
if you wanted to fill down
=IF(ISERR(SMALL(IF(data1>0,data1),ROWS(A$1:A1))),"",SMALL(IF(data1>0,data1),ROWS(A$1:A1)))
as zbor points out it doesnt matter about the orientation of the named range

**Novosedoff** · 12-28-2014, 11:47 AM

Originally Posted by zbor

Did you confirmed with ctrl+shift+enter (not just enter)?

See above attachment where it works.

Well. I may be a little slow, but not that stupid :-) I know what array formulae in Excel mean and how they are entered.

Anyway, my problem is not actual any longer. My Russian mates have helped me out with the right answer a little quicker. In case someone needs this in the future, I attach the spreadsheet with the formula that WORKS FINE for me.

**martindwilson** · 12-28-2014, 11:51 AM

well if you mentioned that you had excel later than 2003 as shown in your profile we would have all suggested iferror() rather than the repetitive if(isna construct
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version.

**zbor** · 12-28-2014, 11:51 AM

oh, I see now what you mean.
Just change ROWS to COLUMNS

Formula:

Please Login or Register  to view this content.

It can be shorten with IFERROR formula but your profile states 2003 so I put like that.

Btw, thanx for sharing your solution too.

**zbor** · 12-28-2014, 11:56 AM

But here:

This will return proper solution no matter how your data is orientated (I put bigger range, not just single row or column) and no matter do you pull formula right or down:

XL2003:

Formula:

Please Login or Register  to view this content.

XL2007+:

Formula:

Please Login or Register  to view this content.

**Novosedoff** · 12-28-2014, 01:53 PM

In case someone would need in the future there is a more universal solution, which looks as follows

=IF(ISERR(SMALL(IF(Data>0,TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(Data))))),TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(Data)))))),"",INDEX(Data,SMALL(IF(Data>0,TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(Data))))),TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(Data)))))))

**Aladin Akyurek** · 12-28-2014, 03:06 PM

Are you sure that works, i.e. the last one with lots of TRANSPOSE calls? Even if it would, it would be very costly...

By the way, the formulas you have in the file you uploaded are not robust: Try to insert new columns in front of the data/formula cells to see this.

**Tony Valko** · 12-28-2014, 03:09 PM

Originally Posted by Novosedoff

In case someone would need in the future there is a more universal solution, which looks as follows

=IF(ISERR(SMALL(IF(Data>0,TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(Data))))),TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(Data)))))),"",INDEX(Data,SMALL(IF(Data>0,TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(Data))))),TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(Data)))))))

I'm not real sure what you're trying to do but looking at that formula I'm certain there has got to be a better way.

**zbor** · 12-28-2014, 05:08 PM

Originally Posted by Tony Valko

I'm not real sure what you're trying to do but looking at that formula I'm certain there has got to be a better way.

Yes, one post above

Here is also non-array solution (only need to take care of errors depending on version):

Formula:

Please Login or Register  to view this content.

Formula for returning only positive values from a range

LinkBack

Thread Tools

Rate This Thread

Display

Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Re: Formula for returning only positive values from a range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] SUMIF formula returning negative value & I need it to be positive

need one formula for returning positive or negative time w/out #######

looking up a master sheet and returning positive values only

Returning only positive values from a function

returning positive values based on dates

Bookmarks

Bookmarks

Posting Permissions