Excel Formula

Try this one:

Answer=IF(AND(A>0, B>0, ...), MIN(B,A, ...), " ") Just drag down.

Cheers

you mean ignoring zero from the set of data? that's something to go around additional equations, above is a wrong one.

Create a second dataset, with zero values in the original set to a number greater than any expected minimum, and find the minimum of the new set.

e.g.

This is the result:

You can hide the second data set if appearance is important.

I'm using OpenOffice Spreadsheet notation here. Use Excel notation for your sheet.

Assuming data column is A1:An

Make a new data column B1:Bn using equation: B1=IF(A1>0,A1,"")

This will transfer any greater-than-zero number and insert a "blank" cell for any zeros or negative numbers.

Then simply find the data minimum using equation: Bn+1=MIN(B1:Bn)

Good luck!

Just a question, why would you like to ignore, zero? zero is significant to minimum function. I mean given the data say, sample Min( 0.02, 0.6, 0.05, 0.0, -0.09)=-0.09

The most practical example where zero is significant- consider a gas analyzer, say for VOC

negative readings are indicated (in the real sense, there is no negative right?) but the reading is indicative that the analyzer you are using is at drift measurement and it needed re-calibration gas to make the correct reference.

Such as these device measures Min & Peak concentration of a gas.

The zero there, is ideally something that stands for the best minimum reading your device could have. Knowing the accuracy of such device say +/-1% of the Span. Then just replace zero reading with +1% if you find the minimum from all the data as zero. (BEST PRACTICAL)

This is actually an easy problem using other programming environment, in C++ this is easy you just have to input the range for you data to ignore zero, but I am not quite familiar with this in MSexcel environment. There must be some way around, doing function with range of values.

It's very interesting to find it out.

You're just insulting the intelligence of the OP. I'm pretty sure that if he didn't need to ignore 0, he wouldn't be asking how to ignore 0. To the OP: The above solutions work easily, depending on what you are doing, you could also write a macro to delete the 0's.

Was that offensive? really. I am sorry if that's the case. I am just trying to explain a far more informative and in practical sense. It's because I think that will be helpful.

Well, I see the OP's not yet complaining. One same thing looks different at different angles by the way, just do not take your side as his/hers.

peace man!

Dear Sir,

if some vendor not responding our request for given a quatation for related project, that time we need to consider the value of zero for relevant vendor cost in excel sheet.

that's why i need to Ignore zero from the minimum value.

You mean like this?

I don't know, perhaps try to explain your problem in a detailed way, so that we may help solve it.

JohnDG's answer is essentially correct, but misses some values.

Use the formula- =if(a2=0," ",a2) for each cell of the "modified value" column and then use the standard =min(b2..bn) formula for the resulting "modified value" column

1st Vender Cost 2 nd Vender Cost 3 rd Vender Cost 4th Vender Cost (Vender not responding)

Design Cost Fabrication Cost Total Design Cost Fabrication Cost Total Design Cost Fabrication Cost Total Design Cost Fabrication Cost Total Minimum cost consider for bidding

5000 7500 12500 5500 8500 14000 4750 8750 13500 0 0 0 what is the formula to find the minimum Cost Value from vendor costing

this is example calculation

please provide the formula to find out the value

Multiple solutions already given to your question. Sorry you are unable to use them.

This uses formulas given above. Please review extensive excel help files if none of these solutions will work for you.

This is easy. The hint is replace 0 number with any alphabet. Could be n, no or any character. Min function ignores any character. You can use my first answer #1 . I'll show you tomorrow how. I am using my phone right now so i have no access to excel. Cheers

Vendors - desg Cost - Fab. Cost - Total - Min.Cost

1st 5000 7500 12500 12500

2nd 5500 8500 14000

3rd 4750 8750 13500

4th 0 0

In the Total Column (D), place the formula:

= If( sum(B2:C2=0,"",sum(B2:C2)) this will place an empty cell where the Zeros are. Copy down to row 5

then in the minimum cost column (E), place the formula:

= if(D2=min(D$2:D$5),D2,"") and copy down to row 5

This will print the lowest value in column E and avoid the Zeroes.

I hope this is OK for you. Or at least if you have another scheme for the MIN() function, you avoid the zeroes.

Like this? I understand. It might be that the design & fabrication could be win by different vendors.

In this case above, you will consider Vendor 3 for the Designing Works, while Vendor 1 for the fabrication works to have the minimum cost amount for the entire project.

But please confirm if this is correct.

Cheers

Dear sir,

1st Vender Cost 2 nd Vender Cost 3 rd Vender Cost 4th Vender Cost (Vender not responding)

Design Cost Fabrication Cost Total Design Cost Fabrication Cost Total Design Cost Fabrication Cost Total Design Cost Fabrication Cost Total Minimum cost consider for bidding

5000 7500 12500 0 0 0 4750 8750 13500 0 0 0 ?

TABLE IN HORIZONTAL POSITION ONLY

Please don't derived calculation in vertical position as per above, because in original my excel calculated file consist of total project equipment and packages costing (lengthy one), therefore it's not possible to provide (calculate elaborately) in vertical position

From your reply, you are working Horizontally: i.e., every vendor has a set of 3 cells, then the next vandor follows on the same row etc... your last column for the result is at the extreme right (Yes?).

Although it looks clumsy to me to have such a sheet, since it is difficult to handle if you have a big number of Vendors, I can give you a formula for 4 vendors in a row ( you can extrapolate for more if you like ):

=IF(SMALL((D6,G6,J6,M6),1)<>0,SMALL((D6,G6,J6,M6),1),IF(SMALL((D6,G6,J6,M6),2)<>0,SMALL((D6,G6,J6,M6),2),IF(SMALL((D6,G6,J6,M6),3)<>0,SMALL((D6,G6,J6,M6),3),IF(SMALL((D6,G6,J6,M6),4)<>0,SMALL((D6,G6,J6,M6),4),"None"))))

The above is on one line and check the '(' & ')' when you extrapolate for more than the 4 vendors! You can change the last message ("None" ) for whatever suits you.

If You want to be more elegant, then you need to write a VB function that will automatically selects and work it out. Short of this, the above does the trick.

Thanks for the reply and GA

Why not use an array formula? It would be an easy solution to your problem. For example, say the range you want to check for the minimum value is C1 to C10. The formula you would enter would be "=MIN(IF(C1:C10<>0,C1:C10))" followed by SHIFT+CTRL+ENTER. The formula would become {=MIN(IF(C1:C10<>0,C1:C10))} on your screen and provide the non-zero min for the range C1:C10.