Returning the Smallest Non-Zero Value

Sunday, 23. September 2007

shantanuo 17:41h

I don't remember if I have mentioned this before, but if you want to return the Smallest Non-Zero Value, use array.
=MIN(IF(A1:A5=0,MAX(A1:A5),A1:A5))

(entered by pressing Ctrl+Shift+Enter)

You can make the formula even shorter by turning it around in this manner:
=MIN(IF(A1:A5<>0,A1:A5))

... Comment

Online for 2506 days
Last modified: 2009.06.20, 07:22

Status

Youre not logged in ... Login

... Home

... Tags

... Galleries

Calendar

Recent updates

tmpdir in MySQL In MySQL, the tmpdir path is mainly used for disk-based sorts (if...

by shantanuo (2009.06.20, 07:22)

Install SQLite $ # As of March 2009, the latest version was 3.6.11. $ wget...

by shantanuo (2009.05.13, 19:24)

Using binlogs to update table It is very interesting to know about how to extract...

by shantanuo (2009.05.11, 21:43)

awk power $ cat salaries Yvette van der Hoff 100000 Sarack Abama 400000 Bernie Madoff...

by shantanuo (2009.05.04, 20:07)

skip networking in MySQL When there is maintenance work to be carried on the database...

by shantanuo (2009.05.03, 21:04)

Saving data to text file sqlite> .mode list sqlite> .separator | sqlite> .output test_file_1.txt sqlite>...

by shantanuo (2009.05.02, 20:31)

Silent mode in Linux Sick of your computer speaker beeping like a demented Morse code...

by shantanuo (2009.04.30, 12:51)

valid date validdate.sh script will look like this... #!/bin/sh userdate=$1 date -d $userdate &> /dev/null...

by shantanuo (2009.04.13, 15:44)

IP addresses from access log generate a list of IP address along with the number...

by shantanuo (2009.04.07, 12:04)

Backup a directory #!/bin/sh tar czvf $1.$(date +%Y%m%d-%H%M%S).tgz $1 exit $? The arc script accepts...

by shantanuo (2009.04.06, 15:47)

Time Zone error on slave Last_Error: Error 'Unknown or incorrect time zone: 'Etc/UTC'' on query....

by shantanuo (2009.03.30, 13:05)

pager to md5sum When optimizing queries by rewriting them to different forms that should return...

by shantanuo (2009.03.30, 13:03)

setting at jobs # at -v 03:00 -f /root/shantanu/testat.sh > /root/shantanu/testat_result.txt 2>&1 # atq 7...

by shantanuo (2009.03.13, 10:07)

better code `test -s /var/log/mailme.txt` if [[ $? -eq 0 ]] ; then The above...

by shantanuo (2009.03.10, 18:15)

read .csv file #!/bin/sh awk -F"," '{print $1,$2}' read1.txt | while read var1 var2 do...

by shantanuo (2009.03.10, 18:03)

common crontab entry 30 0 * * * updatedb > /dev/null 2>&1 # Clock synchronization...

by shantanuo (2009.03.10, 10:20)

Declare your assets! The declare command can be helpful in identifying variables, environmental or otherwise....

by shantanuo (2009.03.01, 18:48)

Innodb troubleshooting When you have your mysql up and running, just connect to any database...

by shantanuo (2009.02.26, 12:59)

Watch replication $ watch --interval=1 --differences 'mysql -uuser -ppassword -e "show slave status\G"'

by shantanuo (2009.02.25, 18:30)

findtable shell script # vi /bin/findtable #!bin/sh mysql -e"select TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_ROWS, CREATE_TIME FROM...

by shantanuo (2009.02.05, 17:31)

processor info Gather information about the processor. This is easily done as follows: # cat...

by shantanuo (2009.01.20, 19:44)

Help Me! You can use the mysql client to perform lookups in the MySQL manual....

by shantanuo (2009.01.02, 20:33)

Show only PHP errors Open /etc/php.ini file # vi /etc/php.ini Set error_reporting as follows: error_reporting...

by shantanuo (2008.12.31, 16:46)

optimize all tables mysql -e"SELECT CONCAT('OPTIMIZE TABLES ', GROUP_CONCAT(TABLE_SCHEMA, '.', TABLE_NAME ORDER BY TABLE_SCHEMA, TABLE_NAME...

by shantanuo (2008.11.11, 17:14)

Unix cheatsheets Cheat sheet act as a reference tool which provides cut and paste kind...

by shantanuo (2008.11.08, 19:30)