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

#### Excel Enthusiast

##### Member
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

• 11.2 KB Views: 9

#### vletm

##### Excel Ninja
Excel Enthusiast
Where are You named the Total of column F,G & H?
Seems that those column .. do not use anywhere ... hmm?

#### Peter Bartholomew

##### Well-Known Member
@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

• 19 KB Views: 7

#### Excel Enthusiast

##### Member
Excel Enthusiast
Where are You named the Total of column F,G & H?
Seems that those column .. do not use anywhere ... hmm?
Dear VLTEM, the total of column will come at the bottom of column F,G & H.

#### Excel Enthusiast

##### Member
@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

#### vletm

##### Excel Ninja
Excel Enthusiast
... bottom ...
I would show totals in top row
... then no need to scroll sheet to see values, which needs to know.

#### Excel Enthusiast

##### Member
Excel Enthusiast
... bottom ...
I would show totals in top row
... then no need to scroll sheet to see values, which needs to know.
Okay i think that's very practical. Thank you.

#### vletm

##### Excel Ninja
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

• 11.5 KB Views: 5

#### deciog

##### Active Member
Peter, good morning.

Great trick I will use in accounting

Hugs

Decio

#### Excel Enthusiast

##### Member
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

#### vletm

##### Excel Ninja
Excel Enthusiast

Which columns have Your named new terms?
You can use same procedure as I have given samples.

#### Peter Bartholomew

##### Well-Known Member
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.

#### Excel Enthusiast

##### Member
Excel Enthusiast
View attachment 62614
Which columns have Your named new terms?
You can use same procedure as I have given samples.
Yes, i've used your example in columns F,G & H. Everything is working fine. Now i can filter the PASS & REJECTED Vehicles and there totals also appear seperately.
Thank you so much. Now i've reached home. Will attach the final sheet in the morning. Thank you once again.

#### Excel Enthusiast

##### Member
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.
Yes, surely will try in the morning.
Thank you, will keep you updated.