Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|