Forumla to exclude cells with a 0 (Zero) 1

[Cordury]

I know there is going to be a simple formula response to this but my brain is too focused on the holidays to come up with a solution.

I have a spreadsheet that contains rates in about 10 columns. I would like to add a column at the end of the spreadsheet that returns the lowest value. However, the data contains zeros therefore the min formula is not working. I have added the following formila =IF(MIN(AO4,AJ4,AE4,Z4,U4,P4,K4)=0,"NO BID",MIN(AO4,AJ4,AE4,Z4,U4,P4,K4)) but that is no help either.

Any suggestions? Basically, I want to return the lowest value that is greater than zero.

Thanks and Happy Holidays!

 

[Zathras]

You almost have it. Just enter your formula as an array formula by pressing Ctrl+Shift+Enter. Excel will automatically mark the formula by placing {} around it, which you will see in the formula window. That should give you the result you want.

 

[Cordury]

Zath,

Brackets now surround my formula but it is still not returning the desired data. I would like the MIN formula to ignore all zeros and return the next lowest number?

 

[Cordury]

Zath,

I have added the "Brackets" to my formula. However, it is still not returning the desired results. I would like the formula to return the next lowest value (that is not a zero).
Thanks,

 

[DaleWatson123321]

Cordury,

The following solution involves additional formulas, but it works.

In columns off to the side, enter the following formula, and then copy it to the next columns - one formula for highest number of possible "0" values. For example, you mentioned 10 columns, so use 10 formulas if there could be 0's in all 10 columns.

=SMALL(($AO4,$AJ4,$AE4,$Z4,$U4,$P4,$K4),1)

(In the above formula, it only references the same 7 cells you had referenced. If you need to reference the 10 columns, then modify the formula)

After you copy the above formula, increment the ,1 - i.e. to ,2 for the 2nd formula, ,3 for the next, etc.

After the above formulas (to the right), enter this formula
=COUNTIF(AV4:BE4,"=0")

After the above formula (to the right), enter this formula:
=SMALL((AO4,AJ4,AE4,Z4,U4,P4,K4),BF4+1)

Then copy the above formulas down for each row of data.

I hope this helps.

Regards, Dale Watson

 

[Cordury]

Dale,

Thanks for taking the time to explain the different forumlas. Yes it is a bire more lengthy than I would have liked but Hey- it works the same.

Again, thanks for taking the time.

 

[Cordury]

I understand why most of your suggestion works, however- Why is the reasoning behind adding 1 to the count of zeros in the last formula?

After the above formula (to the right), enter this formula:
=SMALL((AO4,AJ4,AE4,Z4,U4,P4,K4),BF4+1)

 

[KenWright]

=IF(MAX(AO4,AJ4,AE4,Z4,U4,P4,K4)=0,"NO BID",MIN(IF(AO4:AO4:AJ4:AJ4:AE4:AE4:Z4:Z4:U4:U444:K4:K4>0,AO4:AO4:AJ4:AJ4:AE4:AE4:Z4:Z4:U4:U444:K4:K4)))

Array entered

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Cordury]

Ken,

Your formula is working (sort of). It is pulling data from columns that I do not want it to pull from. I was hoping to return the lowest value (excluding zero) from $AO3,$AJ3,$AE3,$Z3,$U3,$P3,$K3


Thanks,
Cord

 

[KenWright]

What columns is it referencing then? I just tried a straight copy and paste and it worked fine?

=IF(MAX(AO3,AJ3,AE3,Z3,U3,P3,K3)=0,"NO BID",MIN(IF(AO3:AO3:AJ3:AJ3:AE3:AE3:Z3:Z3:U3:U333:K3:K3>0,AO3:AO3:AJ3:AJ3:AE3:AE3:Z3:Z3:U3:U333:K3:K3)))

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[DaleWatson123321]

Cordury,

In answer to your question...

Why is the reasoning behind adding 1 to the count of zeros in the last formula?

After the above formula (to the right), enter this formula:
=SMALL((AO4,AJ4,AE4,Z4,U4,P4,K4),BF4+1)

...The reason is to cause the SMALL function to choose "1" greater than the number of "0"'s (counted by the COUNTIF function in BF4)

I hope this makses sense.

Regards, Dale Watson

 

[Zathras]

The problem with my first formula is that zeros and empty cells are treated differently. I only had blanks, not zeros. Sorry about that.

Try this formula. It is a "simple" formula, i.e. not an array formula. You wanted 10 columns, so I included columns H, I and J in addition to your original 7. I think this will give you what you want with no additional columns of intermediate results needed. If your bids are larger than 999 then you need to make the appropriate adjustment to the formula.

=IF(SUM(AO4,AJ4,AE4,Z4,U4,P4,K4,H4,I4,J4)=0,"NO BID",MIN(IF(AO4,AO4,999),IF(AJ4,AJ4,999),IF(AE4,AE4,999),IF(Z4,Z4,999),IF(U4,U4,999),IF(P4,P4,999),IF(K4,K4,999),IF(H4,H4,999),IF(I4,I4,999),IF(J4,J4,999)))

 

[TonyJollans]

Hi Cordury,

Can I pitch in with a formula?

[blue][tt] =IF(MAX(AO4,AJ4,AE4,Z4,U4,P4,K4)=0,
"NO BID",
IF(MIN(AO4,AJ4,AE4,Z4,U4,P4,K4)>0,
MIN(AO4,AJ4,AE4,Z4,U4,P4,K4),
LARGE((AO4,AJ4,AE4,Z4,U4,P4,K4),RANK(0,(AO4,AJ4,AE4,Z4,U4,P4,K4),0)-1)
)
)[/tt][/blue]

Ken,

I wasn't going to post mine after I saw yours

.. but .. I don't recognise the syntax you're using. What range does AO3:AO3:AJ3:AJ3:AE3:AE3:Z3:Z3:U3:U333:K3:K3 refer to? When I try and do anything with a range like it, it seems to cover the entire range from the top left cell in the list to the bottom right one.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Cordury]

Ken,

Columns AO,AJ,AE,Z,U,P,K contain an internal company metric. Your formula will pull the lowest value (greater than zero) from columns other than AO,AJ,AE,Z,U,P,K. I apologize if I am doing a poor job explaning my issue.

Your formula =IF(MAX(AO3,AJ3,AE3,Z3,U3,P3,K3)=0,"NO BID",MIN(IF(AO3:AO3:AJ3:AJ3:AE3:AE3:Z3:Z3:U3:U333:K3:K3>0,AO3:AO3:AJ3:AJ3:AE3:AE3:Z3:Z3:U3:U333:K3:K3))) contains a number of other references which contain the values uses to calculate my metrics in columns AO,AJ,AE,Z,U,P,K.

Maybe I just need to adjust the references in the second part of yuor formula.

 

[KenWright]

Mine doesn't work. Visually when I edit the formula it appears to refer to just the cells listed and allows me to enter it as a formula but I made the silly mistake of not testing it with any data between the referenced cells, and even though it appears to reference the non-contiguous cells listed, it pulls data from the all the cells between the start cell to finish cell.

Apologies for the bum steer folks.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

And I'm assuming you folks are using Excel 2000 or 97, because when I just tried it in there it seems to highlight the whole range, whereas in Excel 2002 and 2003 it doesn't - grrrrrrrr :-(

If I'd tested in a lower version of Excel I would have seen problems straight away - Can't understand why it does what it does in 2002/3, and even though it appears to be doing something, isn't!!!!!!!!!

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[TonyJollans]

Thanks, Ken,

I did wonder if it was a version issue, but that wasn't the difference I expected.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[KenWright]

LOL - Serves me right for deserting my old friend SUMPRODUCT:-

=IF(MAX(AO3,AJ3,AE3,Z3,U3,P3,K3)=0,"NO BID",LARGE((AO3,AJ3,AE3,Z3,U3,P3,K3),SUMPRODUCT((MOD(COLUMN($K$3:$AO$3),5)=1)*($K$3:$AO$3<>0))))


Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------