• 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.

I need to change a positive number to negative using text PASS & REJECTED condition.

What i need is that when i select PASS in column N, then the number in column F,G & H should be as it is (Positive) & if i select REJECTED in column N, then the number should change to a negative number. So that the rejected material's No. of Bags, Mill Wgt & FCI Wgt is not added but is deducted from the Total of column F,G & H.
Tried many combinations of IF, AND OR but could not accomplish it.
 

Attachments

  • OUTWARD.xlsx
    11.2 KB · Views: 9
@Excel Enthusiast
You seem to be using Tables but, as yet, have made little use of Structured Referencing.
If one considers the column 'No. of Bags' it appears to be manually input data so it cannot be changed by a formula. What one could do is define a named formula that will take the absolute value from the number of bags entry and the sign from the 'Status' field. This can be done by creating the name 'No.ofBags' in Name Manager and letting it refer to
= IF(Table1[Status]="Pass", 1, -1) * ABS(Table1[No. of Bags])

Then, the formula
= SUM(No.ofBags)
will take deductions into account.

There are further 'tricks' that can be played but the danger is that you will sometime in the future forget and catch yourself out. As it stands, the count of bags to be deducted still shows as positive despite the fact that you intend to subtract them. It is possible to use Conditional Formatting to show the number preceded by a minus sign, despite the fact that it was entered as positive. The condition would be
=$N4="Reject"
and the number format would be
-0;-0;;@
To hide any negative numbers input by the user within these columns, one could use the default number format
0;0;;@
 

Attachments

  • OUTWARD (PB).xlsx
    19 KB · Views: 7
@Excel Enthusiast
You seem to be using Tables but, as yet, have made little use of Structured Referencing.
If one considers the column 'No. of Bags' it appears to be manually input data so it cannot be changed by a formula. What one could do is define a named formula that will take the absolute value from the number of bags entry and the sign from the 'Status' field. This can be done by creating the name 'No.ofBags' in Name Manager and letting it refer to
= IF(Table1[Status]="Pass", 1, -1) * ABS(Table1[No. of Bags])

Then, the formula
= SUM(No.ofBags)
will take deductions into account.

There are further 'tricks' that can be played but the danger is that you will sometime in the future forget and catch yourself out. As it stands, the count of bags to be deducted still shows as positive despite the fact that you intend to subtract them. It is possible to use Conditional Formatting to show the number preceded by a minus sign, despite the fact that it was entered as positive. The condition would be
=$N4="Reject"
and the number format would be
-0;-0;;@
To hide any negative numbers input by the user within these columns, one could use the default number format
0;0;;@


Thank You Very much PB. You have been a great help. Will do the same with column G & H. Will let you know about the final result of my workbook.
Thank You once again
 
Excel Enthusiast
I'm still wondering ... hmm?
... why need to change something to negative? ... gotta always try be positive!
... why there should be 'common' positive (PASS) and negative (REJECTED) texts? ... text seems to be Reject.
... ... Do You normally add + in front of values? Now, You could see at once, which rows are Reject.
I tested Your possible needs ... with this combination.
( I took PASS away and added totals to top with basic formulas. )
 

Attachments

  • OUTWARD.xlsx
    11.5 KB · Views: 5
Excel Enthusiast
I'm still wondering ... hmm?
... why need to change something to negative? ... gotta always try be positive!
... why there should be 'common' positive (PASS) and negative (REJECTED) texts? ... text seems to be Reject.
... ... Do You normally add + in front of values? Now, You could see at once, which rows are Reject.
I tested Your possible needs ... with this combination.
( I took PASS away and added totals to top with basic formulas. )

Well VLETM i need to change the Rejected Rice from godown Stock, as we have to submit report of all the modes, i.e. Inward Paddy, Outward Rice & Rejected Rice
 
If you wish to have a total in-between the table header row and its data range it is possible to hide the header and create a copy of it above the newly-defined totals row.
 
Back
Top