Help on a formula
 

Columns A, B, and C are prices. Column D is the number of each object I
need. I want to take the least expensive in each row and multiply it by D.
However, the least in A, B, and C cannot be 0.00. In the end, it will cost
to but these objects.

I've tried =$D1*(MIN(IF($A1:$C1<0,$A1:$C1,Z52))) just for the 1st row, and
it gives me a Value error at the 1st $A1:$C1.

Any ideas on how to get the total?

A B C D

0.04 0.07 0.05 10
0.17 0.83 0.22
1.36 0.79 0.04 1
0.82 0.69 0.70
1.97 1.32 1.31 1
0.20 1.68 1.49 1
2.84 2.57 3.16
2.84 2.57 3.16 2
0.00 2.57 0.00
0.88 1.92 2.10
1.74 0.51 0.22
2.24 1.83 2.46
0.05 0.83 0.08
0.47 0.68 0.18
0.47 0.68 0.43
3.97 4.19 2.62
0.86 0.71 0.51
0.97 0.23 0.32
0.84 0.72 1.30

Help on a formula
 

Since you mentioned "prices" I'm assuming that there will not be any
negative numbers.

Try this array formula** :

=IF(SUM(A1:C1),D1*MIN(IF(A1:C10,A1:C1)),"")

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Heather" wrote in message
...
Columns A, B, and C are prices. Column D is the number of each object I
need. I want to take the least expensive in each row and multiply it by
D.
However, the least in A, B, and C cannot be 0.00. In the end, it will
cost
to but these objects.

I've tried =$D1*(MIN(IF($A1:$C1<0,$A1:$C1,Z52))) just for the 1st row,
and
it gives me a Value error at the 1st $A1:$C1.

Any ideas on how to get the total?

A B C D

0.04 0.07 0.05 10
0.17 0.83 0.22
1.36 0.79 0.04 1
0.82 0.69 0.70
1.97 1.32 1.31 1
0.20 1.68 1.49 1
2.84 2.57 3.16
2.84 2.57 3.16 2
0.00 2.57 0.00
0.88 1.92 2.10
1.74 0.51 0.22
2.24 1.83 2.46
0.05 0.83 0.08
0.47 0.68 0.18
0.47 0.68 0.43
3.97 4.19 2.62
0.86 0.71 0.51
0.97 0.23 0.32
0.84 0.72 1.30

Help on a formula
 

Use this array formula

=$D1*(MIN(IF($A1:$C1<0,$A1:$C1)))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Heather" wrote in message
...
Columns A, B, and C are prices. Column D is the number of each object I
need. I want to take the least expensive in each row and multiply it by
D.
However, the least in A, B, and C cannot be 0.00. In the end, it will
cost
to but these objects.

I've tried =$D1*(MIN(IF($A1:$C1<0,$A1:$C1,Z52))) just for the 1st row,
and
it gives me a Value error at the 1st $A1:$C1.

Any ideas on how to get the total?

A B C D

0.04 0.07 0.05 10
0.17 0.83 0.22
1.36 0.79 0.04 1
0.82 0.69 0.70
1.97 1.32 1.31 1
0.20 1.68 1.49 1
2.84 2.57 3.16
2.84 2.57 3.16 2
0.00 2.57 0.00
0.88 1.92 2.10
1.74 0.51 0.22
2.24 1.83 2.46
0.05 0.83 0.08
0.47 0.68 0.18
0.47 0.68 0.43
3.97 4.19 2.62
0.86 0.71 0.51
0.97 0.23 0.32
0.84 0.72 1.30

Help on a formula
 

Heather,

Assuming that Z52 is a number greater than the price range then try array
entering your formula , (Ctrl + Shift + Enter not just Enter), as it is.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Heather" wrote in message
...
Columns A, B, and C are prices. Column D is the number of each object I
need. I want to take the least expensive in each row and multiply it by
D.
However, the least in A, B, and C cannot be 0.00. In the end, it will
cost
to but these objects.

I've tried =$D1*(MIN(IF($A1:$C1<0,$A1:$C1,Z52))) just for the 1st row,
and
it gives me a Value error at the 1st $A1:$C1.

Any ideas on how to get the total?

A B C D

0.04 0.07 0.05 10
0.17 0.83 0.22
1.36 0.79 0.04 1
0.82 0.69 0.70
1.97 1.32 1.31 1
0.20 1.68 1.49 1
2.84 2.57 3.16
2.84 2.57 3.16 2
0.00 2.57 0.00
0.88 1.92 2.10
1.74 0.51 0.22
2.24 1.83 2.46
0.05 0.83 0.08
0.47 0.68 0.18
0.47 0.68 0.43
3.97 4.19 2.62
0.86 0.71 0.51
0.97 0.23 0.32
0.84 0.72 1.30

Help on a formula
 

If a price is 0, then transform it into a large price, something like (A1=0)
*999+A1. So the formula can be:
=$D1 * MIN(($A1=0)*999+$A1,($B1=0)*999+$B1,($C1=0)*999+$C 1)

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200707/1