| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with formula
DATA COD Marime Intrate Price Ramase
12/2/09 8167 44-46 10 57.68 5 21/1/09 8167 44-46 10 58.08 0 7/1/09 8167 44-46 10 54.85 10 Formula is =SUMIFS(E:E,Stoc!B:B,Comenzi!D8,C:C,Comenzi!E8,F:F ,"0",A:A,MIN(A:A)) but not work. I want the next date or next price if F:F = 0. If F:F=0, result 0. needing a formulas TKS |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
help with formula
This one makes more sense.
Where these conditions are true: The formula is array-entered using CTRL+SHIFT+ENTER The distance between eligible rows is never more than 4999 rows (if this is not the case, change the 5000s to 10000s or more). You meant the next date/price that meets all conditions implied by your formula. The references to Comenzi are meant to be absolute. The COD column is all text and the comenzi reference is text OR the COD column is all numbers and the comenzi reference is numbers In cell G2: The formula for date is: =IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDE X(A2:A5000,MATCH(1,(F2:$F5000<0)*(B2:B5000=Comenz i!$D$8)*(C2:C5000=Comenzi!$E$8),0)),A2) The formula for price is: =IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDE X(E2:E5000,MATCH(1,(F2:$F5000<0)*(B2:B5000=Comenz i!$D$8)*(C2:C5000=Comenzi!$E$8),0)),E2) "nicuolt" wrote: DATA COD Marime Intrate Price Ramase 12/2/09 8167 44-46 10 57.68 5 21/1/09 8167 44-46 10 58.08 0 7/1/09 8167 44-46 10 54.85 10 Formula is =SUMIFS(E:E,Stoc!B:B,Comenzi!D8,C:C,Comenzi!E8,F:F ,"0",A:A,MIN(A:A)) but not work. I want the next date or next price if F:F = 0. If F:F=0, result 0. needing a formulas TKS |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
help with formula
Not work!
My formula is in the sheet comenzi. I want to calcul next price except date with RAMASE 0 . In this case, rezult 58.08... 21/1/09 < 12/2/09, and in row (7/1/09 8167 44-46 10 54.85 0), RAMASE = 0 Help with this formula! Tanks in avans! Ex. =IF(AND(F:F=0,B:B=Comenzi!$D$8,Stoc!C:C=Comenzi!$E $8),INDEX(Stoc!E:E,MATCH(1,(Stoc!F:F<0)*(Stoc!B:B =Comenzi!D8)*(Stoc!C:C=Comenzi!E8),0)),Stoc!E:E) ?????? = 58.08 The references to Comenzi are meant to be absolute. "~L" wrote: This one makes more sense. Where these conditions are true: The formula is array-entered using CTRL+SHIFT+ENTER The distance between eligible rows is never more than 4999 rows (if this is not the case, change the 5000s to 10000s or more). You meant the next date/price that meets all conditions implied by your formula. The references to Comenzi are meant to be absolute. The COD column is all text and the comenzi reference is text OR the COD column is all numbers and the comenzi reference is numbers In cell G2: The formula for date is: =IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDE X(A2:A5000,MATCH(1,(F2:$F5000<0)*(B2:B5000=Comenz i!$D$8)*(C2:C5000=Comenzi!$E$8),0)),A2) The formula for price is: =IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDE X(E2:E5000,MATCH(1,(F2:$F5000<0)*(B2:B5000=Comenz i!$D$8)*(C2:C5000=Comenzi!$E$8),0)),E2) "nicuolt" wrote: DATA COD Marime Intrate Price Ramase 12/2/09 8167 44-46 10 57.68 5 21/1/09 8167 44-46 10 58.08 10 7/1/09 8167 44-46 10 54.85 0 Formula is =SUMIFS(E:E,Stoc!B:B,Comenzi!D8,C:C,Comenzi!E8,F:F ,"0",A:A,MIN(A:A)) but not work. I want the next date or next price if F:F = 0. If F:F=0, result 0. needing a formulas TKS |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
help with formula
DATA COD Marime Intrate Price Ramase
12/2/09 8167 44-46 10 57.68 5 21/1/09 8167 44-46 10 58.08 10 7/1/09 8167 44-46 10 54.85 0 7/1/09 8044 42 7 51.21 7 IF search in tabel: Cod 8167, Marime 44-46, sum = 15 in RAMASE. I want extract 10 (max with this price "58.08"). In formula result this price: "58.08". Other cod is for other search! I'm sure is posible! "nicu" wrote: Not work! My formula is in the sheet comenzi. I want to calcul next price except date with RAMASE 0 . In this case, rezult 58.08... 21/1/09 < 12/2/09, and in row (7/1/09 8167 44-46 10 54.85 0), RAMASE = 0 Help with this formula! Tanks in avans! Ex. =IF(AND(F:F=0,B:B=Comenzi!$D$8,Stoc!C:C=Comenzi!$E $8),INDEX(Stoc!E:E,MATCH(1,(Stoc!F:F<0)*(Stoc!B:B =Comenzi!D8)*(Stoc!C:C=Comenzi!E8),0)),Stoc!E:E) ?????? = 58.08 The references to Comenzi are meant to be absolute. "~L" wrote: This one makes more sense. Where these conditions are true: The formula is array-entered using CTRL+SHIFT+ENTER The distance between eligible rows is never more than 4999 rows (if this is not the case, change the 5000s to 10000s or more). You meant the next date/price that meets all conditions implied by your formula. The references to Comenzi are meant to be absolute. The COD column is all text and the comenzi reference is text OR the COD column is all numbers and the comenzi reference is numbers In cell G2: The formula for date is: =IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDE X(A2:A5000,MATCH(1,(F2:$F5000<0)*(B2:B5000=Comenz i!$D$8)*(C2:C5000=Comenzi!$E$8),0)),A2) The formula for price is: =IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDE X(E2:E5000,MATCH(1,(F2:$F5000<0)*(B2:B5000=Comenz i!$D$8)*(C2:C5000=Comenzi!$E$8),0)),E2) "nicuolt" wrote: DATA COD Marime Intrate Price Ramase 12/2/09 8167 44-46 10 57.68 5 21/1/09 8167 44-46 10 58.08 10 7/1/09 8167 44-46 10 54.85 0 Formula is =SUMIFS(E:E,Stoc!B:B,Comenzi!D8,C:C,Comenzi!E8,F:F ,"0",A:A,MIN(A:A)) but not work. I want the next date or next price if F:F = 0. If F:F=0, result 0. needing a formulas TKS |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
help with formula
Array enter:
=MAX(IF((Stoc!F:F<0)*(Stoc!B:B=Comenzi!D8)*(Stoc! C:C=Comenzi!E8),Stoc!F:F)) "nicu" wrote: DATA COD Marime Intrate Price Ramase 12/2/09 8167 44-46 10 57.68 5 21/1/09 8167 44-46 10 58.08 10 7/1/09 8167 44-46 10 54.85 0 7/1/09 8044 42 7 51.21 7 IF search in tabel: Cod 8167, Marime 44-46, sum = 15 in RAMASE. I want extract 10 (max with this price "58.08"). In formula result this price: "58.08". Other cod is for other search! I'm sure is posible! "nicu" wrote: Not work! My formula is in the sheet comenzi. I want to calcul next price except date with RAMASE 0 . In this case, rezult 58.08... 21/1/09 < 12/2/09, and in row (7/1/09 8167 44-46 10 54.85 0), RAMASE = 0 Help with this formula! Tanks in avans! Ex. =IF(AND(F:F=0,B:B=Comenzi!$D$8,Stoc!C:C=Comenzi!$E $8),INDEX(Stoc!E:E,MATCH(1,(Stoc!F:F<0)*(Stoc!B:B =Comenzi!D8)*(Stoc!C:C=Comenzi!E8),0)),Stoc!E:E) ?????? = 58.08 The references to Comenzi are meant to be absolute. "~L" wrote: This one makes more sense. Where these conditions are true: The formula is array-entered using CTRL+SHIFT+ENTER The distance between eligible rows is never more than 4999 rows (if this is not the case, change the 5000s to 10000s or more). You meant the next date/price that meets all conditions implied by your formula. The references to Comenzi are meant to be absolute. The COD column is all text and the comenzi reference is text OR the COD column is all numbers and the comenzi reference is numbers In cell G2: The formula for date is: =IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDE X(A2:A5000,MATCH(1,(F2:$F5000<0)*(B2:B5000=Comenz i!$D$8)*(C2:C5000=Comenzi!$E$8),0)),A2) The formula for price is: =IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDE X(E2:E5000,MATCH(1,(F2:$F5000<0)*(B2:B5000=Comenz i!$D$8)*(C2:C5000=Comenzi!$E$8),0)),E2) "nicuolt" wrote: DATA COD Marime Intrate Price Ramase 12/2/09 8167 44-46 10 57.68 5 21/1/09 8167 44-46 10 58.08 10 7/1/09 8167 44-46 10 54.85 0 Formula is =SUMIFS(E:E,Stoc!B:B,Comenzi!D8,C:C,Comenzi!E8,F:F ,"0",A:A,MIN(A:A)) but not work. I want the next date or next price if F:F = 0. If F:F=0, result 0. needing a formulas TKS |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
help with formula
Not work. Result = 0 with formula
=MAX(IF((Stoc!F:F<0)*(Stoc!B:B=Comenzi!D8)*(Stoc! C:C=Comenzi!E8),Stoc!F:F)) Is posibble??: IF(AND("COD"=Comenzi!D8,"Marime"=Comenzi!E8,F:F0) ,MIN(A:A EXCEPT ROW( DATE with F = 0) and rezult is PRICE???... in this case 58.08 Or "Next" PRICE after NEXT date when F = 0??? with criteria Cod and Marime? "~L" wrote: Array enter: =MAX(IF((Stoc!F:F<0)*(Stoc!B:B=Comenzi!D8)*(Stoc! C:C=Comenzi!E8),Stoc!F:F)) "nicu" wrote: DATA COD Marime Intrate Price Ramase 12/2/09 8167 44-46 10 57.68 5 21/1/09 8167 44-46 10 58.08 10 7/1/09 8167 44-46 10 54.85 0 7/1/09 8044 42 7 51.21 7 IF search in tabel: Cod 8167, Marime 44-46, sum = 15 in RAMASE. I want extract 10 (max with this price "58.08"). In formula result this price: "58.08". Other cod is for other search! I'm sure is posible! "nicu" wrote: Not work! My formula is in the sheet comenzi. I want to calcul next price except date with RAMASE 0 . In this case, rezult 58.08... 21/1/09 < 12/2/09, and in row (7/1/09 8167 44-46 10 54.85 0), RAMASE = 0 Help with this formula! Tanks in avans! Ex. =IF(AND(F:F=0,B:B=Comenzi!$D$8,Stoc!C:C=Comenzi!$E $8),INDEX(Stoc!E:E,MATCH(1,(Stoc!F:F<0)*(Stoc!B:B =Comenzi!D8)*(Stoc!C:C=Comenzi!E8),0)),Stoc!E:E) ?????? = 58.08 The references to Comenzi are meant to be absolute. "~L" wrote: This one makes more sense. Where these conditions are true: The formula is array-entered using CTRL+SHIFT+ENTER The distance between eligible rows is never more than 4999 rows (if this is not the case, change the 5000s to 10000s or more). You meant the next date/price that meets all conditions implied by your formula. The references to Comenzi are meant to be absolute. The COD column is all text and the comenzi reference is text OR the COD column is all numbers and the comenzi reference is numbers In cell G2: The formula for date is: =IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDE X(A2:A5000,MATCH(1,(F2:$F5000<0)*(B2:B5000=Comenz i!$D$8)*(C2:C5000=Comenzi!$E$8),0)),A2) The formula for price is: =IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDE X(E2:E5000,MATCH(1,(F2:$F5000<0)*(B2:B5000=Comenz i!$D$8)*(C2:C5000=Comenzi!$E$8),0)),E2) "nicuolt" wrote: DATA COD Marime Intrate Price Ramase 12/2/09 8167 44-46 10 57.68 5 21/1/09 8167 44-46 10 58.08 10 7/1/09 8167 44-46 10 54.85 0 Formula is =SUMIFS(E:E,Stoc!B:B,Comenzi!D8,C:C,Comenzi!E8,F:F ,"0",A:A,MIN(A:A)) but not work. I want the next date or next price if F:F = 0. If F:F=0, result 0. needing a formulas TKS |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
help with formula
I have tested the formulas before replying. The result of the formula with
the data you provided should be 10. It is 0 when the terms do not match or when it is not array entered. Does the formula: =Comenzi!D8=Stoc!B2 produce TRUE? Does =comenzi!E8=Stoc!C2 produce TRUE? Which do you prefer: the MAX price or the next price after the 0? I think the formula you are describing now is: =INDEX(F:F,MATCH(MIN(IF((B:B=Comenzi!D8)*(C:C=Come nzi!E8)*(F:F0),A:A)),A:A,0)) Which you must array-enter like before with control+shift+enter. But that does not return the max price or the 'next' price, it returns the price at the earliest date meeting all of those conditions. "nicu" wrote: Not work. Result = 0 with formula =MAX(IF((Stoc!F:F<0)*(Stoc!B:B=Comenzi!D8)*(Stoc! C:C=Comenzi!E8),Stoc!F:F)) Is posibble??: IF(AND("COD"=Comenzi!D8,"Marime"=Comenzi!E8,F:F0) ,MIN(A:A EXCEPT ROW( DATE with F = 0) and rezult is PRICE???... in this case 58.08 Or "Next" PRICE after NEXT date when F = 0??? with criteria Cod and Marime? "~L" wrote: Array enter: =MAX(IF((Stoc!F:F<0)*(Stoc!B:B=Comenzi!D8)*(Stoc! C:C=Comenzi!E8),Stoc!F:F)) "nicu" wrote: DATA COD Marime Intrate Price Ramase 12/2/09 8167 44-46 10 57.68 5 21/1/09 8167 44-46 10 58.08 10 7/1/09 8167 44-46 10 54.85 0 7/1/09 8044 42 7 51.21 7 IF search in tabel: Cod 8167, Marime 44-46, sum = 15 in RAMASE. I want extract 10 (max with this price "58.08"). In formula result this price: "58.08". Other cod is for other search! I'm sure is posible! "nicu" wrote: Not work! My formula is in the sheet comenzi. I want to calcul next price except date with RAMASE 0 . In this case, rezult 58.08... 21/1/09 < 12/2/09, and in row (7/1/09 8167 44-46 10 54.85 0), RAMASE = 0 Help with this formula! Tanks in avans! Ex. =IF(AND(F:F=0,B:B=Comenzi!$D$8,Stoc!C:C=Comenzi!$E $8),INDEX(Stoc!E:E,MATCH(1,(Stoc!F:F<0)*(Stoc!B:B =Comenzi!D8)*(Stoc!C:C=Comenzi!E8),0)),Stoc!E:E) ?????? = 58.08 The references to Comenzi are meant to be absolute. "~L" wrote: This one makes more sense. Where these conditions are true: The formula is array-entered using CTRL+SHIFT+ENTER The distance between eligible rows is never more than 4999 rows (if this is not the case, change the 5000s to 10000s or more). You meant the next date/price that meets all conditions implied by your formula. The references to Comenzi are meant to be absolute. The COD column is all text and the comenzi reference is text OR the COD column is all numbers and the comenzi reference is numbers In cell G2: The formula for date is: =IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDE X(A2:A5000,MATCH(1,(F2:$F5000<0)*(B2:B5000=Comenz i!$D$8)*(C2:C5000=Comenzi!$E$8),0)),A2) The formula for price is: =IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDE X(E2:E5000,MATCH(1,(F2:$F5000<0)*(B2:B5000=Comenz i!$D$8)*(C2:C5000=Comenzi!$E$8),0)),E2) "nicuolt" wrote: DATA COD Marime Intrate Price Ramase 12/2/09 8167 44-46 10 57.68 5 21/1/09 8167 44-46 10 58.08 10 7/1/09 8167 44-46 10 54.85 0 Formula is =SUMIFS(E:E,Stoc!B:B,Comenzi!D8,C:C,Comenzi!E8,F:F ,"0",A:A,MIN(A:A)) but not work. I want the next date or next price if F:F = 0. If F:F=0, result 0. needing a formulas TKS |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
help with formula
You are the best!
thank you very very much! "~L" wrote: I have tested the formulas before replying. The result of the formula with the data you provided should be 10. It is 0 when the terms do not match or when it is not array entered. Does the formula: =Comenzi!D8=Stoc!B2 produce TRUE? Does =comenzi!E8=Stoc!C2 produce TRUE? Which do you prefer: the MAX price or the next price after the 0? I think the formula you are describing now is: =INDEX(F:F,MATCH(MIN(IF((B:B=Comenzi!D8)*(C:C=Come nzi!E8)*(F:F0),A:A)),A:A,0)) Which you must array-enter like before with control+shift+enter. But that does not return the max price or the 'next' price, it returns the price at the earliest date meeting all of those conditions. "nicu" wrote: Not work. Result = 0 with formula =MAX(IF((Stoc!F:F<0)*(Stoc!B:B=Comenzi!D8)*(Stoc! C:C=Comenzi!E8),Stoc!F:F)) Is posibble??: IF(AND("COD"=Comenzi!D8,"Marime"=Comenzi!E8,F:F0) ,MIN(A:A EXCEPT ROW( DATE with F = 0) and rezult is PRICE???... in this case 58.08 Or "Next" PRICE after NEXT date when F = 0??? with criteria Cod and Marime? "~L" wrote: Array enter: =MAX(IF((Stoc!F:F<0)*(Stoc!B:B=Comenzi!D8)*(Stoc! C:C=Comenzi!E8),Stoc!F:F)) "nicu" wrote: DATA COD Marime Intrate Price Ramase 12/2/09 8167 44-46 10 57.68 5 21/1/09 8167 44-46 10 58.08 10 7/1/09 8167 44-46 10 54.85 0 7/1/09 8044 42 7 51.21 7 IF search in tabel: Cod 8167, Marime 44-46, sum = 15 in RAMASE. I want extract 10 (max with this price "58.08"). In formula result this price: "58.08". Other cod is for other search! I'm sure is posible! "nicu" wrote: Not work! My formula is in the sheet comenzi. I want to calcul next price except date with RAMASE 0 . In this case, rezult 58.08... 21/1/09 < 12/2/09, and in row (7/1/09 8167 44-46 10 54.85 0), RAMASE = 0 Help with this formula! Tanks in avans! Ex. =IF(AND(F:F=0,B:B=Comenzi!$D$8,Stoc!C:C=Comenzi!$E $8),INDEX(Stoc!E:E,MATCH(1,(Stoc!F:F<0)*(Stoc!B:B =Comenzi!D8)*(Stoc!C:C=Comenzi!E8),0)),Stoc!E:E) ?????? = 58.08 The references to Comenzi are meant to be absolute. "~L" wrote: This one makes more sense. Where these conditions are true: The formula is array-entered using CTRL+SHIFT+ENTER The distance between eligible rows is never more than 4999 rows (if this is not the case, change the 5000s to 10000s or more). You meant the next date/price that meets all conditions implied by your formula. The references to Comenzi are meant to be absolute. The COD column is all text and the comenzi reference is text OR the COD column is all numbers and the comenzi reference is numbers In cell G2: The formula for date is: =IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDE X(A2:A5000,MATCH(1,(F2:$F5000<0)*(B2:B5000=Comenz i!$D$8)*(C2:C5000=Comenzi!$E$8),0)),A2) The formula for price is: =IF(AND(F2=0,B2=Comenzi!$D$8,C2=Comenzi!$E$8),INDE X(E2:E5000,MATCH(1,(F2:$F5000<0)*(B2:B5000=Comenz i!$D$8)*(C2:C5000=Comenzi!$E$8),0)),E2) "nicuolt" wrote: DATA COD Marime Intrate Price Ramase 12/2/09 8167 44-46 10 57.68 5 21/1/09 8167 44-46 10 58.08 10 7/1/09 8167 44-46 10 54.85 0 Formula is =SUMIFS(E:E,Stoc!B:B,Comenzi!D8,C:C,Comenzi!E8,F:F ,"0",A:A,MIN(A:A)) but not work. I want the next date or next price if F:F = 0. If F:F=0, result 0. needing a formulas TKS |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
![nicuolt[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
