| 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
![nicuolt[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
