How can I make Excel's MIN function ignore zeroes in a set?

Posted by mfg on Stack Overflow See other posts from Stack Overflow or by mfg
Published on 2011-01-07T17:44:51Z Indexed on 2011/01/07 17:53 UTC
Read the original article Hit count: 300

Filed under:

excel

|

min

In Excel, I have the following formula =(MIN(H69,H52,H35,H18)*(1/H18))*10 that is supposed to return the MIN of a range, and divide it by the current cell (*(1/H18) ), then multiply by 10.

I am having difficulty with adding a type of NULLIF statement. I want to be able to have (the possibility for) blank rows, and have the MIN function ignore zero/blank fields while selecting the next lowest value (all are between 1.0-0.1).

Is there a modifier i can apply to the MIN function to make it not compare zeroes in the MIN set? Is there a better funtion than MIN to use?

This is to prevent the following situation: users will need to eliminate fields that are zeros from the form, theres 2 formula edits per entry, averaging 4 entries per use, so 8 possible errors per form use...

© Stack Overflow or respective owner

Related posts about excel

Excel error "This workbook contains Excel 4.0 macros or Excel 5.0 modules"

as seen on Super User - Search for 'Super User'
I have a workbook that was protected via the Protect Workbook feature. It was sent to someone else to modify. When they sent it back, it was unprotected and when I try to reprotect it I get this error, "This workbook contains Excel 4.0 macros or Excel 5.0 modules. If you would like to… >>> More
Open excel 2007 excel files and save as 97-2003 formats in VBA

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a weird situation where I have a set of excel files, all having the extension .xls., in a directory where I can open all of them just fine in Excel 2007. The odd thing is that I cannot open them in Excel 2003, on the same machine, without opening the file first in 2007 and going and saving… >>> More
Creating Excel or Excel compatible Spreadsheets on the server side in C#

as seen on Stack Overflow - Search for 'Stack Overflow'
I'd like to make server-side excel compatible spreadsheets that maybe use OpenXML or a structured data format. I've used Office Interop before to generate Excel spreadsheets, but those apps run on a PC that has office installed. For this web project I'm building, the server doesn't have office installed… >>> More
vba access values from excel workbook from another excel workbook

as seen on Stack Overflow - Search for 'Stack Overflow'
Any ideas why this code will not work? Workbooks.Open Filename:="C:\a.xls" Workbooks("a.xls").Activate Worksheets("b").Select Dim OutputArray(10, 10) OutputArray(1, 1) = Workbooks("c.xls").Worksheets("worksheet_otherthan_default").Select.Range(A1).Value I'm trying to run a macro in one file (unnamed… >>> More
Excel workbook event order and usage when closing Excel

as seen on Stack Overflow - Search for 'Stack Overflow'
Given the following workbook events: BeforeClose BeforeSave Please tell me: - The firing order in the case of multiple workbooks alreay opened (wb1, wb2 and wb3 are opened in this order) and the user closes Excel. You can assume all 3 needs saving. - What happen if user cancels one of the saving… >>> More

Related posts about min

Getting the index of the returned max or min item using max()/min() on a list

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm using Python's max and min functions on lists for a minimax algorithm, and I need the index of the value returned by max() or min(). In other words, I need to know which move produced the max (at a first player's turn) or min (second player) value. for i in range(9): newBoard = currentBoard… >>> More
Hello World Problem in Eclipse with Android 2.3 used min 7 instead of min 9

as seen on Stack Overflow - Search for 'Stack Overflow'
*/I am Having a Similar Problem.......... Any Suggestions/ package com.example.helloandroid6; import android.app.Activity; import android.os.Bundle; import android.widget.TextView; public class HelloAndroid extends Activity { /** Called when the activity is first created. */ @Override public void… >>> More
Super Joybox 5 HID 0925:8884 not recognized as joystick in Ubuntu 12.04 LTS

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
Problem: When using the "Super JoyBox 5" 4 port playstation 2 to USB adapter, the device is not recognized as a joystick. there is no js0 created, but instead another input eventX and mouseX are created in /dev/input. When using the directional buttons (up down left right) on a Playstation 1 controller… >>> More
Avoid Multiple Queries When Querying for Max and Min Values

as seen on Internet.com - Search for 'Internet.com'
Instead of making one query to get the Max and Min values for a column, and then making another query to get additional information about those rows, you can do it all in one query. >>> More
sensors reporting weird temperatures

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
lm-sensors is reporting weird temps for me: $ sensors coretemp-isa-0000 Adapter: ISA adapter Core 0: +38.0°C (high = +72.0°C, crit = +100.0°C) coretemp-isa-0001 Adapter: ISA adapter Core 1: +35.0°C (high = +72.0°C, crit = +100.0°C) coretemp-isa-0002 Adapter: ISA adapter Core 2:… >>> More