Error using MIN across a row array when trying to exclude zero

  • Thread starter Thread starter Felicia
  • Start date Start date
F

Felicia

Ref: Knowledge Base article 60116. The formula supplied
doesn't work in my case -- apparently because I'm using a
row array instead of a column array.

I have months in columns (ex. G = Jan, H = Feb, etc) and
expense items in rows: (ex. row 19 = my water bill, with
each month's amount in the proper column) Using the
formula =MIN(IF(G19:R19>0,G19:R19)) as described in the
KB article, I would expect to see my smallest month's
bill that was greater than zero. Instead I get a #VALUE!
error. I have Excel 2002.

Any ideas?
 
That is because you have to enter the formula with ctrl + shift & enter
another way to get it without having to use ctrl + shift & enter is this
formula

=LARGE(G19:R19,COUNTIF(G19:R19,">0"))

(don't be confused by the LARGE function, it will return the smallest value
greater than zero)
 
Felicia

that is an Array Formula. You need to press Ctrl-Shift-Enter rather than
just Enter. If you do this, you will see the formula surrounded by "curly
brackets" thus: {=MIN(IF(G19:R19>0,G19:R19))}

Regards

Trevor
 
That did it! Vielen dank'.
-----Original Message-----
Hi
you have to enter this as array formula (with CTRL+SHIFT+ENTER)

--
Regards
Frank Kabel
Frankfurt, Germany


.
Click to expand...
 
Thanks also, Peo and Trevor.
-----Original Message-----
That is because you have to enter the formula with ctrl + shift & enter
another way to get it without having to use ctrl + shift & enter is this
formula

=LARGE(G19:R19,COUNTIF(G19:R19,">0"))

(don't be confused by the LARGE function, it will return the smallest value
greater than zero)

--

Regards,

Peo Sjoblom




.
Click to expand...
 
Back
Top