• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Offsetting Negative and Positive Values in Excel worksheet

S

srini82

New Member
Hi All,

Just trying to find a way out to offset negative and positive values in a excel worksheet.

I have found a lot of formulas to do that if i have 1 match for each value. However I have few different combinations as given below in a single column.

-100

+100

+100

-225

-225

+225

+225

+225


Now how to possibly assign a formula in such a case, to get only one row after knocking off the other matching rows.
 
I'm not completely sure what you were wanting, but how about this?

=COUNTIF(A:A,-A2)<COUNTIF(A$2:A2,A2)

Anywhere you see a TRUE is a number/row that doesn't have a match.
 
To build on what Luke M said; if you want to see the values rather than "True" or "False in the second column put:

=IF(COUNTIF(A:A,-A4)<COUNTIF(A$2:A4,A4),A4,"")
 
Thanks Luke & Ole for your prompt help.


In my case, Ole's formula worked perfectly.


Once again, thanks to both of you.


Cheers!
 
You must log in or register to reply here.
Back
Top