A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Divide by the minimum non-zero number in a range of cells



 
 
Thread Tools Display Modes
  #11  
Old June 13th, 2009, 05:35 PM posted to microsoft.public.excel.worksheet.functions
Jarek Kujawa[_2_]
external usenet poster
 
Posts: 775
Default Divide by the minimum non-zero number in a range of cells

corrected:

=MIN(IF(A1:A10,A1:A10))



On 13 Cze, 12:10, Jarek Kujawa wrote:
=MIN(IF(A1:A10;A1:A10))

this is an array-formula so CTRL+SHIFT+ENTER instead of simply
entering it

On 13 Cze, 11:13, Opus wrote:



I need to find the minimum non-zero number in a range of cells that contains
some zeroes. *So that I can divide by the minimum number. *Any ideas?


If I just use the MIN function, it returns a value of 0, then when my
formula divides by that I get the divide by zero error.- Ukryj cytowany tekst -


- Pokaż cytowany tekst -


  #12  
Old June 13th, 2009, 05:38 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Divide by the minimum non-zero number in a range of cells

If you don't want an array formula

Assuming there will always be at least 1 non-zero cell and there's no text
in the range:

=MIN(INDEX(((A1:A70)*A1:A7)+((A1:A7=0)*1E+100),, 1))

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

If you don't want an array formula try this

=SUMPRODUCT((A1:A70)*(MIN(A1:A7)=A1:A7)*A1:A7)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Opus" wrote:

I need to find the minimum non-zero number in a range of cells that
contains
some zeroes. So that I can divide by the minimum number. Any ideas?

If I just use the MIN function, it returns a value of 0, then when my
formula divides by that I get the divide by zero error.



  #13  
Old June 13th, 2009, 08:53 PM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
Posts: 3,722
Default Divide by the minimum non-zero number in a range of cells

=MIN(INDEX(10^10-(A1:A70)*(10^10-A1:A7),))

Just press ENTER


"Opus" wrote:

I need to find the minimum non-zero number in a range of cells that contains
some zeroes. So that I can divide by the minimum number. Any ideas?

If I just use the MIN function, it returns a value of 0, then when my
formula divides by that I get the divide by zero error.

  #14  
Old June 13th, 2009, 10:08 PM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_3_]
external usenet poster
 
Posts: 3,333
Default Divide by the minimum non-zero number in a range of cells

Well that's pretty interesting, unfortunately I cleared my test range and
can't duplicate the original results.

Alternative:

=SUMPRODUCT(MIN(9^9*(A1:A8=0)+A1:A8))

hopefully this is a little better.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Shane Devenshire" wrote:

Hi,

If you don't want an array formula try this

=SUMPRODUCT((A1:A70)*(MIN(A1:A7)=A1:A7)*A1:A7)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Opus" wrote:

I need to find the minimum non-zero number in a range of cells that contains
some zeroes. So that I can divide by the minimum number. Any ideas?

If I just use the MIN function, it returns a value of 0, then when my
formula divides by that I get the divide by zero error.

  #15  
Old June 13th, 2009, 11:16 PM posted to microsoft.public.excel.worksheet.functions
opus
external usenet poster
 
Posts: 4
Default Divide by the minimum non-zero number in a range of cells

Thank you all for your replies. This is what occured to me late last night
and what I wound up doing:

I realized that what I was looking for here was the Maximum number resulting
from dividing a constant by the Minimum number in a range. So I did

=IF(A1=0,"",Constant/A1)

and copied it for each cell in my original range creating a range of results
in a separate column B that I later hid. This weeded out all the cells
containing zero.

I then used

=Max(B1:B10)

to obtain the result I was actually after.


"Opus" wrote:

I need to find the minimum non-zero number in a range of cells that contains
some zeroes. So that I can divide by the minimum number. Any ideas?

If I just use the MIN function, it returns a value of 0, then when my
formula divides by that I get the divide by zero error.

  #16  
Old June 14th, 2009, 12:11 AM posted to microsoft.public.excel.worksheet.functions
joeu2004
external usenet poster
 
Posts: 1,748
Default Divide by the minimum non-zero number in a range of cells

"T. Valko" wrote:
=MIN(INDEX(((A1:A70)*A1:A7)+((A1:A7=0)*1E+100),, 1))


I think this is the most reliable expressions of this ilk; "reliable"
because I believe it works for all values in A1:A7 less then 1E100, even if
MIN(A1:A7) is 2^53 or more less than 1E100.

However, I believe that expression fails if MIN(A1:A7) is greater than
1E100. Moreover, it can be simplified. I believe the following is more
reliable:

=min(index(A1:A7+2*max(A1:A7)*(A1:A7=0),,1))

I believe that works as long as MAX(A1:A7) is less than 2^1023.

We could extend the allowed numerical range for A1:A7 somewhat by replacing
"2*" with a smaller factor larger than 1; the smallest constant factor is
1.00000000000001. But it does not seem to be worth the trouble.

PS: Personally, I don't feel that any of this is worth the trouble just to
avoid the straight-forward array formula, as much as I dislike array
formulas in general.


----- original message -----

"T. Valko" wrote in message
...
If you don't want an array formula


Assuming there will always be at least 1 non-zero cell and there's no text
in the range:

=MIN(INDEX(((A1:A70)*A1:A7)+((A1:A7=0)*1E+100),, 1))

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

If you don't want an array formula try this

=SUMPRODUCT((A1:A70)*(MIN(A1:A7)=A1:A7)*A1:A7)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Opus" wrote:

I need to find the minimum non-zero number in a range of cells that
contains
some zeroes. So that I can divide by the minimum number. Any ideas?

If I just use the MIN function, it returns a value of 0, then when my
formula divides by that I get the divide by zero error.




  #17  
Old June 14th, 2009, 12:26 AM posted to microsoft.public.excel.worksheet.functions
joeu2004
external usenet poster
 
Posts: 1,748
Default Divide by the minimum non-zero number in a range of cells

"Shane Devenshire" wrote:
=SUMPRODUCT(MIN(9^9*(A1:A8=0)+A1:A8))


Why 9^9? Why not something more straight-forward like 10^10? That can
written as the constant 1E10?

And why is this any better than Biff's (T.Valko's) formulation that uses
MIN(INDEX(...))? I presume you saw his, since you posted your follow-up
more than 4 hours later to the same news server, I believe.

In any case, as I noted regarding Biff's formula, expressions of this ilk
limit MAX(A1:A8) to whatever factor you use, be it 9^9, 1E10 or 1E100. So
it would behoove you to use a larger factor like 1E10 or even 1E100.

And to that end, I think it would be better to replace any constant factor
(e.g. 9^9) with 2*max(A1:A8). That limits MAX(A1:A8) to 2^1023, about half
the biggest value allowed.

I also noted that we can extend the range of values for MAX(A1:A8) by
replacing the factor 2 with a smaller factor larger than 1; the smallest
constant factor is 1.00000000000001.

But none of this seems to be worth the trouble just to avoid the
straight-forward array formula solution, as much as I don't like array
formulas myself.


----- original message -----

"Shane Devenshire" wrote in
message ...
Well that's pretty interesting, unfortunately I cleared my test range and
can't duplicate the original results.

Alternative:

=SUMPRODUCT(MIN(9^9*(A1:A8=0)+A1:A8))

hopefully this is a little better.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Shane Devenshire" wrote:

Hi,

If you don't want an array formula try this

=SUMPRODUCT((A1:A70)*(MIN(A1:A7)=A1:A7)*A1:A7)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Opus" wrote:

I need to find the minimum non-zero number in a range of cells that
contains
some zeroes. So that I can divide by the minimum number. Any ideas?

If I just use the MIN function, it returns a value of 0, then when my
formula divides by that I get the divide by zero error.


  #18  
Old June 14th, 2009, 12:41 AM posted to microsoft.public.excel.worksheet.functions
joeu2004
external usenet poster
 
Posts: 1,748
Default Divide by the minimum non-zero number in a range of cells

"Teethless mama" wrote:
=MIN(INDEX(10^10-(A1:A70)*(10^10-A1:A7),))


As I noted in my comments about Biff's (T.Valko's) formula, expressions of
this ilk limit MAX(A1:A7) to whatever factor you use, 10^10 in your case.
(Why not simply write 1E10?)

But your expression suffers from the additional limitation that MIN(A1:A7)
cannot be more than about 2^52 less than 1E10 or whatever factor you choose.

Arguably, since the OP is worried about a minimum of zero (sometimes?), you
might think that MIN(A1:A7) cannot be 2^52 less than 1E10. (But is the
operative word "sometimes"?) But considering my first comment, ironically
it behooves you to choose a larger factor, unless the OP gives us an upper
bound.

Much ado about nothing. Personally, I don't feel that any of this is worth
the trouble just to avoid the straight-forward array formula, as much as I
dislike array formulas in general.


---- original message -----

"Teethless mama" wrote in message
...
=MIN(INDEX(10^10-(A1:A70)*(10^10-A1:A7),))

Just press ENTER


"Opus" wrote:

I need to find the minimum non-zero number in a range of cells that
contains
some zeroes. So that I can divide by the minimum number. Any ideas?

If I just use the MIN function, it returns a value of 0, then when my
formula divides by that I get the divide by zero error.


  #19  
Old June 14th, 2009, 01:58 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Divide by the minimum non-zero number in a range of cells

PS: Personally, I don't feel that any of this is worth the
trouble just to avoid the straight-forward array formula


I agree completely. It was just an academic exercise.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"T. Valko" wrote:
=MIN(INDEX(((A1:A70)*A1:A7)+((A1:A7=0)*1E+100),, 1))


I think this is the most reliable expressions of this ilk; "reliable"
because I believe it works for all values in A1:A7 less then 1E100, even
if MIN(A1:A7) is 2^53 or more less than 1E100.

However, I believe that expression fails if MIN(A1:A7) is greater than
1E100. Moreover, it can be simplified. I believe the following is more
reliable:

=min(index(A1:A7+2*max(A1:A7)*(A1:A7=0),,1))

I believe that works as long as MAX(A1:A7) is less than 2^1023.

We could extend the allowed numerical range for A1:A7 somewhat by
replacing "2*" with a smaller factor larger than 1; the smallest constant
factor is 1.00000000000001. But it does not seem to be worth the trouble.

PS: Personally, I don't feel that any of this is worth the trouble just
to avoid the straight-forward array formula, as much as I dislike array
formulas in general.


----- original message -----

"T. Valko" wrote in message
...
If you don't want an array formula


Assuming there will always be at least 1 non-zero cell and there's no
text in the range:

=MIN(INDEX(((A1:A70)*A1:A7)+((A1:A7=0)*1E+100),, 1))

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

If you don't want an array formula try this

=SUMPRODUCT((A1:A70)*(MIN(A1:A7)=A1:A7)*A1:A7)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Opus" wrote:

I need to find the minimum non-zero number in a range of cells that
contains
some zeroes. So that I can divide by the minimum number. Any ideas?

If I just use the MIN function, it returns a value of 0, then when my
formula divides by that I get the divide by zero error.






  #20  
Old June 14th, 2009, 02:05 AM posted to microsoft.public.excel.worksheet.functions
joeu2004
external usenet poster
 
Posts: 1,748
Default Divide by the minimum non-zero number in a range of cells

"T. Valko" wrote:
PS: Personally, I don't feel that any of this is worth the
trouble just to avoid the straight-forward array formula


I agree completely. It was just an academic exercise.


Likewise. Always fun ;-). Also gives us a chance to learn new approaches.


----- original message -----

"T. Valko" wrote in message
...
PS: Personally, I don't feel that any of this is worth the
trouble just to avoid the straight-forward array formula


I agree completely. It was just an academic exercise.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"T. Valko" wrote:
=MIN(INDEX(((A1:A70)*A1:A7)+((A1:A7=0)*1E+100),, 1))


I think this is the most reliable expressions of this ilk; "reliable"
because I believe it works for all values in A1:A7 less then 1E100, even
if MIN(A1:A7) is 2^53 or more less than 1E100.

However, I believe that expression fails if MIN(A1:A7) is greater than
1E100. Moreover, it can be simplified. I believe the following is more
reliable:

=min(index(A1:A7+2*max(A1:A7)*(A1:A7=0),,1))

I believe that works as long as MAX(A1:A7) is less than 2^1023.

We could extend the allowed numerical range for A1:A7 somewhat by
replacing "2*" with a smaller factor larger than 1; the smallest constant
factor is 1.00000000000001. But it does not seem to be worth the
trouble.

PS: Personally, I don't feel that any of this is worth the trouble just
to avoid the straight-forward array formula, as much as I dislike array
formulas in general.


----- original message -----

"T. Valko" wrote in message
...
If you don't want an array formula

Assuming there will always be at least 1 non-zero cell and there's no
text in the range:

=MIN(INDEX(((A1:A70)*A1:A7)+((A1:A7=0)*1E+100),, 1))

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

If you don't want an array formula try this

=SUMPRODUCT((A1:A70)*(MIN(A1:A7)=A1:A7)*A1:A7)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Opus" wrote:

I need to find the minimum non-zero number in a range of cells that
contains
some zeroes. So that I can divide by the minimum number. Any ideas?

If I just use the MIN function, it returns a value of 0, then when my
formula divides by that I get the divide by zero error.






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:44 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.