| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
negative value
Hi,
I have below data with me, i have 4 columns where the "no of days" for Employee ID (under a particular manager ID) is coming with -ve value as well as with +ve value. The "no of days" are automatically coming whether it is -ve or +ve comes in the report in column "D". I want a formula in column "E" where it will find for me the Emp ID having +ve value that is offsetting with the same Emp ID having the -ve value Manager Empl ID no of days -ve value comment ID 454 12121 5 No 454 12121 15 No 454 12121 -5 Yes 454 12121 4 No 454 12121 -4 Yes 454 12121 10 No 878 45464 14 No 878 45464 -2 Yes 878 45464 2 No Regards, rkp |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
negative value
Put this formula into cell E2 and fill it down. Adjust the ranges as needed,
I've just shown from row 2 to row 10, and I'm sure your list is longer than just 10 entries. =IF(C2<0,"Matching +ve is at: " & ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2),"") "Radhakant Panigrahi" wrote: Hi, I have below data with me, i have 4 columns where the "no of days" for Employee ID (under a particular manager ID) is coming with -ve value as well as with +ve value. The "no of days" are automatically coming whether it is -ve or +ve comes in the report in column "D". I want a formula in column "E" where it will find for me the Emp ID having +ve value that is offsetting with the same Emp ID having the -ve value Manager Empl ID no of days -ve value comment ID 454 12121 5 No 454 12121 15 No 454 12121 -5 Yes 454 12121 4 No 454 12121 -4 Yes 454 12121 10 No 878 45464 14 No 878 45464 -2 Yes 878 45464 2 No Regards, rkp |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
negative value
To work properly when there is no matching +ve entry in the list, use this
formula instead: =IF(C2<0,"Matching +ve is at: " & IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"no match yet",ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"") Once more, adjust the ranges involved to match the length of your data columns. "Radhakant Panigrahi" wrote: Hi, I have below data with me, i have 4 columns where the "no of days" for Employee ID (under a particular manager ID) is coming with -ve value as well as with +ve value. The "no of days" are automatically coming whether it is -ve or +ve comes in the report in column "D". I want a formula in column "E" where it will find for me the Emp ID having +ve value that is offsetting with the same Emp ID having the -ve value Manager Empl ID no of days -ve value comment ID 454 12121 5 No 454 12121 15 No 454 12121 -5 Yes 454 12121 4 No 454 12121 -4 Yes 454 12121 10 No 878 45464 14 No 878 45464 -2 Yes 878 45464 2 No Regards, rkp |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
negative value
Hello JLatham,
i tried the both the formullae, but did not worked...if i look into the data i need to find the below IMP ID in column "E" where i have written "Offset" where as your formula giving comment against the -ve value... i want to identify the +value of IMP ID that is offsetting the -ve value of the same EMP ID Manager IDEMP ID No of days '-ve value' Comments Comments 454 12121 5 No Offset 454 12121 15 No 454 12121 -5 Yes Matching +ve is at: $B$2 454 12121 4 No Offset 454 12121 -4 Yes Matching +ve is at: $B$5 454 12121 10 No 878 45464 14 No 878 45464 -2 Yes Matching +ve is at: $B$10 878 45464 2 No Offset Regards, rkp "JLatham" wrote: To work properly when there is no matching +ve entry in the list, use this formula instead: =IF(C2<0,"Matching +ve is at: " & IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"no match yet",ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"") Once more, adjust the ranges involved to match the length of your data columns. "Radhakant Panigrahi" wrote: Hi, I have below data with me, i have 4 columns where the "no of days" for Employee ID (under a particular manager ID) is coming with -ve value as well as with +ve value. The "no of days" are automatically coming whether it is -ve or +ve comes in the report in column "D". I want a formula in column "E" where it will find for me the Emp ID having +ve value that is offsetting with the same Emp ID having the -ve value Manager Empl ID no of days -ve value comment ID 454 12121 5 No 454 12121 15 No 454 12121 -5 Yes 454 12121 4 No 454 12121 -4 Yes 454 12121 10 No 878 45464 14 No 878 45464 -2 Yes 878 45464 2 No Regards, rkp |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
negative value
The second formula worked very well for me with your sample data; there may
be some small thing keeping it from working at your end. If you can, send me a copy of your workbook as an email attachment to (remove spaces) Help From @JLatham Site. com and I will attempt to set the formula up to work properly in your workbook. "Radhakant Panigrahi" wrote: Hello JLatham, i tried the both the formullae, but did not worked...if i look into the data i need to find the below IMP ID in column "E" where i have written "Offset" where as your formula giving comment against the -ve value... i want to identify the +value of IMP ID that is offsetting the -ve value of the same EMP ID Manager IDEMP ID No of days '-ve value' Comments Comments 454 12121 5 No Offset 454 12121 15 No 454 12121 -5 Yes Matching +ve is at: $B$2 454 12121 4 No Offset 454 12121 -4 Yes Matching +ve is at: $B$5 454 12121 10 No 878 45464 14 No 878 45464 -2 Yes Matching +ve is at: $B$10 878 45464 2 No Offset Regards, rkp "JLatham" wrote: To work properly when there is no matching +ve entry in the list, use this formula instead: =IF(C2<0,"Matching +ve is at: " & IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"no match yet",ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"") Once more, adjust the ranges involved to match the length of your data columns. "Radhakant Panigrahi" wrote: Hi, I have below data with me, i have 4 columns where the "no of days" for Employee ID (under a particular manager ID) is coming with -ve value as well as with +ve value. The "no of days" are automatically coming whether it is -ve or +ve comes in the report in column "D". I want a formula in column "E" where it will find for me the Emp ID having +ve value that is offsetting with the same Emp ID having the -ve value Manager Empl ID no of days -ve value comment ID 454 12121 5 No 454 12121 15 No 454 12121 -5 Yes 454 12121 4 No 454 12121 -4 Yes 454 12121 10 No 878 45464 14 No 878 45464 -2 Yes 878 45464 2 No Regards, rkp |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
negative value
I believe I understand better now. Try this formula instead of the other one
and let me know if this does what you need it to do. As before, this formula would go into a cell on Row 2 and you need to change the range references to match your data. =IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"",IF(C20,"Offsetting -ve is at: " & IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"",ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)) & " for " & B2,"")) "Radhakant Panigrahi" wrote: Hello JLatham, i tried the both the formullae, but did not worked...if i look into the data i need to find the below IMP ID in column "E" where i have written "Offset" where as your formula giving comment against the -ve value... i want to identify the +value of IMP ID that is offsetting the -ve value of the same EMP ID Manager IDEMP ID No of days '-ve value' Comments Comments 454 12121 5 No Offset 454 12121 15 No 454 12121 -5 Yes Matching +ve is at: $B$2 454 12121 4 No Offset 454 12121 -4 Yes Matching +ve is at: $B$5 454 12121 10 No 878 45464 14 No 878 45464 -2 Yes Matching +ve is at: $B$10 878 45464 2 No Offset Regards, rkp "JLatham" wrote: To work properly when there is no matching +ve entry in the list, use this formula instead: =IF(C2<0,"Matching +ve is at: " & IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"no match yet",ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"") Once more, adjust the ranges involved to match the length of your data columns. "Radhakant Panigrahi" wrote: Hi, I have below data with me, i have 4 columns where the "no of days" for Employee ID (under a particular manager ID) is coming with -ve value as well as with +ve value. The "no of days" are automatically coming whether it is -ve or +ve comes in the report in column "D". I want a formula in column "E" where it will find for me the Emp ID having +ve value that is offsetting with the same Emp ID having the -ve value Manager Empl ID no of days -ve value comment ID 454 12121 5 No 454 12121 15 No 454 12121 -5 Yes 454 12121 4 No 454 12121 -4 Yes 454 12121 10 No 878 45464 14 No 878 45464 -2 Yes 878 45464 2 No Regards, rkp |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
negative value
And if you only want the word "offset" to appear when the conditions are met,
then: =IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"",IF(C20,IF(ISERR(ADDRE SS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"","OFFSET"),"")) "Radhakant Panigrahi" wrote: Hello JLatham, i tried the both the formullae, but did not worked...if i look into the data i need to find the below IMP ID in column "E" where i have written "Offset" where as your formula giving comment against the -ve value... i want to identify the +value of IMP ID that is offsetting the -ve value of the same EMP ID Manager IDEMP ID No of days '-ve value' Comments Comments 454 12121 5 No Offset 454 12121 15 No 454 12121 -5 Yes Matching +ve is at: $B$2 454 12121 4 No Offset 454 12121 -4 Yes Matching +ve is at: $B$5 454 12121 10 No 878 45464 14 No 878 45464 -2 Yes Matching +ve is at: $B$10 878 45464 2 No Offset Regards, rkp "JLatham" wrote: To work properly when there is no matching +ve entry in the list, use this formula instead: =IF(C2<0,"Matching +ve is at: " & IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"no match yet",ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"") Once more, adjust the ranges involved to match the length of your data columns. "Radhakant Panigrahi" wrote: Hi, I have below data with me, i have 4 columns where the "no of days" for Employee ID (under a particular manager ID) is coming with -ve value as well as with +ve value. The "no of days" are automatically coming whether it is -ve or +ve comes in the report in column "D". I want a formula in column "E" where it will find for me the Emp ID having +ve value that is offsetting with the same Emp ID having the -ve value Manager Empl ID no of days -ve value comment ID 454 12121 5 No 454 12121 15 No 454 12121 -5 Yes 454 12121 4 No 454 12121 -4 Yes 454 12121 10 No 878 45464 14 No 878 45464 -2 Yes 878 45464 2 No Regards, rkp |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing color of negative bar when using Invert if negative | Charts and Charting in Excel | |||
| I want to get negative hours if subtraction of hours is negative | Excel Worksheet Functions | |||
| Set negative numbers to zero. Do not calculate with negative valu | Excel Discussion (Misc queries) | |||
| Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) | |||
| 2003= negative number&2004= negative number How Do I Calculate gro | Excel Worksheet Functions | |||
Linear Mode
