1

I need some help with a formula. I'm trying to work out the minimum value if greater than -365 but less that 365.

{=MIN(IF((E46:J46<365)*(E46:J46>-365),E46:J46))}

This is the formula I have so far, which is ignoring anything less that -365 and anything over 365.

It gives me the correct value if the minimum is less than 0 but anything greater than 0 is getting ignored, and the cell displays a 0.

For example,

E -42101
F 80
G -35
H 367
I "blank"
J "blank"

Then I will get -35 as my minimum value, if I delete this from the cell my minimum values turns to 0, when it should be 80.

Thank you in advance.

atame
  • 521
  • 2
  • 12
  • 22

3 Answers3

3

Try:

=AGGREGATE(15,6,IF(NOT(ISNUMBER(myRng)),NA(),IF(myRng>365,NA(),IF(myRng<-365,NA(),myRng))),1)

entered with ctrl + shift + enter

MIN does not ignore errors, but AGGREGATE can.

For a normally entered formula, provoked by @XORLX below:

=AGGREGATE(15,6,1/(ISNUMBER(myRng)*(myRng>-365)*(myRng<365))*myRng,1)

or, more succinctly:

=AGGREGATE(15,6,myRng/(ISNUMBER(myRng)*(myRng>-365)*(myRng<365)),1)

To avoid returning #NUM! if there are no entries meeting the criteria, one solution is to use IFERROR:

=IFERROR(AGGREGATE(15,6,myRng/(ISNUMBER(myRng)*(myRng<365)*(myRng>-365)),1),"")
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • AGGREGATE with CSE kind of defeats the point of that function, no? – XOR LX Oct 07 '16 at 12:38
  • @XORLX Yeah, but it works, and retains the value of zero – Ron Rosenfeld Oct 07 '16 at 12:40
  • Sure, but with reciprocation we avoid the need for all the value_if_false clauses and also the need for CSE: =AGGREGATE(15,6,myRng/(ISNUMBER(myRng)*(myRng<365)*(myRng>-365)),1) – XOR LX Oct 07 '16 at 12:46
  • @XORLX I worked that out as you were posting it. Thanks – Ron Rosenfeld Oct 07 '16 at 12:48
  • @RonRosenfeld Hi, both worked, but it is having the same issues as with my min. If there is no min that matches the criteria, the cell value is 0 i need it to be blank. Thanks – atame Oct 07 '16 at 12:51
  • @atame Here the formula returns `#NUM!` if there is no min that matches the criteria. What, exactly, is in your cells? – Ron Rosenfeld Oct 07 '16 at 13:02
  • @RonRosenfeld Since we're using AGGREGATE, we can do without the ISNUMBER test: =AGGREGATE(15,6,myRng/((myRng<365)*(myRng>-365)),1) – XOR LX Oct 07 '16 at 13:03
  • @RonRosenfeld. the 6 cells can contain any negative number or any positive number. Thanks – atame Oct 07 '16 at 13:05
  • @atame No formulas? Please provide a set of values that returns a `0` where there are no values meeting the requirement. – Ron Rosenfeld Oct 07 '16 at 13:09
  • @RonRosenfeld. apologies I mixed that up with the Min formula, the aggregate it giving me a #NUM!, how can i get this to be blank. Thanks – atame Oct 07 '16 at 13:11
  • @atame Just "wrap" it with an `IFERROR` formula – Ron Rosenfeld Oct 07 '16 at 13:11
  • 1
    @XORLX I don't think you can avoid the `ISNUMBER`. `AGGREGATE` is evaluating the array produced by the division, and not the values in `myRng`. So an empty cell will evaluate to `{TRUE,TRUE}` and the division will evaluate to `0` – Ron Rosenfeld Oct 07 '16 at 13:19
  • @RonRosenfeld. Thank you, worked great, out of curiosity, do you know how to resolve the displayed 0 for `{=MIN(IF((E46:J46<365)*(E46:J46>-365)*NOT(ISBLANK(E46:J46)),E46:J46))}`. Thanks – atame Oct 07 '16 at 14:21
  • @atame You can first test all the cells to see if any meet the requirements: `=IF(AND((myRng>=365)*(myRng<=-365)+ISBLANK(myRng)),"",MIN(IF((myRng<365)*(myRng>-365)*NOT(ISBLANK(myRng)),myRng)))` – Ron Rosenfeld Oct 07 '16 at 15:24
2

Like proposed in the comments, adding a check on whether the cell is blank or not should be sufficient to resolve your issue:

{=MIN(IF((E46:J46<365)*(E46:J46>-365)*(E46:J46<>""),E46:J46))}

(Entered with Ctrl-Shift-Enter)

Maxime Goguillon
  • 21
  • 2
  • Thanks for that, pretty similar to what i worked out. =MIN(IF((E46:J46<365)*(E46:J46>-365)*NOT(ISBLANK(E46:J46)),E‌​46:J46)). My problem now is that if there is no minimum that meets the criteria it needs to display nothing, rather than the 0 it currently is. any thoughts? – atame Oct 07 '16 at 12:52
  • Well, I tried several stuff but I couldn't find better than the aggregate() method raised by @Ron above... I'll keep on searching though – Maxime Goguillon Oct 07 '16 at 15:39
  • @atame you need to be careful with my solution: In the case you wrap this in an if() function to check if it returns 0, it'll work **but** if you actually have a _0_ in your list wich is the minimum valid value, it'll return _blank_ (instead of _0_)... – Maxime Goguillon Oct 07 '16 at 15:42
0

With help from Ron Rosenfeld and Maxime Goguillon who have provided both great answers.

Ron Rosenfeld

{=IFERROR(AGGREGATE(15,6,E46:J46/((E46:J46)*(E46:J46>-365)*(E46:J46<365))*E46:J46,1),"")}

Maxime Goguillon

{=IF(MIN(IF((E46:J46<365)*(E46:J46>-365)*NOT(ISBLANK(E46:J46)),E46:J46))=0,"",MIN(IF((E46:J46<365)*(E46:J46>-365)*NOT(ISBLANK(E46:J46)),E46:J46)))}

Both of these work perfectly.

The help was much appreciated!!!!

atame
  • 521
  • 2
  • 12
  • 22