A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

negative value



 
 
Thread Tools Display Modes
  #1  
Old April 29th, 2010, 04:48 PM posted to microsoft.public.excel.worksheet.functions
Radhakant Panigrahi
external usenet poster
 
Posts: 43
Default 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  
Old April 29th, 2010, 07:36 PM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default 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(C20,"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  
Old April 29th, 2010, 07:41 PM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default negative value

To work properly when there is no matching +ve entry in the list, use this
formula instead:

=IF(C20,"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  
Old April 30th, 2010, 06:16 AM posted to microsoft.public.excel.worksheet.functions
Radhakant Panigrahi
external usenet poster
 
Posts: 43
Default 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(C20,"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  
Old April 30th, 2010, 06:31 AM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default 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(C20,"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  
Old April 30th, 2010, 03:04 PM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default 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(C20,"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  
Old April 30th, 2010, 03:08 PM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default 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(C20,"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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.