Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Looking for formula for lowest costs, excluding zero
I found a formula for determining the lowest costs from several columns, but
I cannot get it to work in my worksheet. This is nearly 1000 lines long. I want to search 4 columns and get the lowest cost excluding 0. Any suggestions would be greatly appreciated. Thanks!! |
#2
|
|||
|
|||
Try this array formula (confirm with Ctrl+Shift+Enter, not just Enter):
=MIN(IF(A1:D1000,A1:D1000)) Regards, KL "Pipeladylu" wrote in message ... I found a formula for determining the lowest costs from several columns, but I cannot get it to work in my worksheet. This is nearly 1000 lines long. I want to search 4 columns and get the lowest cost excluding 0. Any suggestions would be greatly appreciated. Thanks!! |
#3
|
|||
|
|||
I tried the formula with the array, but it still did not work. The formula
that I used was =MIN(IF(MOD(COLUMN(B5:G5),3)=1,IF(B5:G5))) Do you think this is correct? "KL" wrote: Try this array formula (confirm with Ctrl+Shift+Enter, not just Enter): =MIN(IF(A1:D1000,A1:D1000)) Regards, KL "Pipeladylu" wrote in message ... I found a formula for determining the lowest costs from several columns, but I cannot get it to work in my worksheet. This is nearly 1000 lines long. I want to search 4 columns and get the lowest cost excluding 0. Any suggestions would be greatly appreciated. Thanks!! |
#4
|
|||
|
|||
Hi,
Your formula makes no sense to me, can you explain what you are trying to achieve and which columns you want to evaluate. REgards, KL "Pipeladylu" wrote in message ... I tried the formula with the array, but it still did not work. The formula that I used was =MIN(IF(MOD(COLUMN(B5:G5),3)=1,IF(B5:G5))) Do you think this is correct? "KL" wrote: Try this array formula (confirm with Ctrl+Shift+Enter, not just Enter): =MIN(IF(A1:D1000,A1:D1000)) Regards, KL "Pipeladylu" wrote in message ... I found a formula for determining the lowest costs from several columns, but I cannot get it to work in my worksheet. This is nearly 1000 lines long. I want to search 4 columns and get the lowest cost excluding 0. Any suggestions would be greatly appreciated. Thanks!! |
#5
|
|||
|
|||
Maybe this:
=MIN(IF((MOD(COLUMN(B5:G5),3)=1)*(B5:G5<0),B5:G5) ) Enter as ARRAY formula Regards, KL "KL" wrote in message ... Hi, Your formula makes no sense to me, can you explain what you are trying to achieve and which columns you want to evaluate. REgards, KL "Pipeladylu" wrote in message ... I tried the formula with the array, but it still did not work. The formula that I used was =MIN(IF(MOD(COLUMN(B5:G5),3)=1,IF(B5:G5))) Do you think this is correct? "KL" wrote: Try this array formula (confirm with Ctrl+Shift+Enter, not just Enter): =MIN(IF(A1:D1000,A1:D1000)) Regards, KL "Pipeladylu" wrote in message ... I found a formula for determining the lowest costs from several columns, but I cannot get it to work in my worksheet. This is nearly 1000 lines long. I want to search 4 columns and get the lowest cost excluding 0. Any suggestions would be greatly appreciated. Thanks!! |
#6
|
|||
|
|||
I have a large quote that I am working on and have pricing from several
vendors. I am nearly complete and would like to create a formula to determine the lowest cost per line. The costs appear in lines H, L, T & U. I don't want zero to become a part of the lowest pricing. Thanks KL!!! "KL" wrote: Hi, Your formula makes no sense to me, can you explain what you are trying to achieve and which columns you want to evaluate. REgards, KL "Pipeladylu" wrote in message ... I tried the formula with the array, but it still did not work. The formula that I used was =MIN(IF(MOD(COLUMN(B5:G5),3)=1,IF(B5:G5))) Do you think this is correct? "KL" wrote: Try this array formula (confirm with Ctrl+Shift+Enter, not just Enter): =MIN(IF(A1:D1000,A1:D1000)) Regards, KL "Pipeladylu" wrote in message ... I found a formula for determining the lowest costs from several columns, but I cannot get it to work in my worksheet. This is nearly 1000 lines long. I want to search 4 columns and get the lowest cost excluding 0. Any suggestions would be greatly appreciated. Thanks!! |
#7
|
|||
|
|||
If you want to determine only the lowest cost for each column (and not the number of times the lowest cost appears) then try this: =IF(MIN(H2:H200)0,SMALL(H2:H200,COUNTIF(H2:H200,0 )+1),MIN(H2:H200)) This example is for column H (range H2:H200) so adjust as necessary and then copy it to to your other columns. It is not an array formula so just enter in normal way -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=468906 |
#8
|
|||
|
|||
Thanks... It works great!! I really appreciate your help
"Cutter" wrote: If you want to determine only the lowest cost for each column (and not the number of times the lowest cost appears) then try this: =IF(MIN(H2:H200)0,SMALL(H2:H200,COUNTIF(H2:H200,0 )+1),MIN(H2:H200)) This example is for column H (range H2:H200) so adjust as necessary and then copy it to to your other columns. It is not an array formula so just enter in normal way -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=468906 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Profit & Loss Account Costs shown as a percentage of Sales | Excel Discussion (Misc queries) |