# remove / calculate negative values

#### liaosy

##### Board Regular
hi everyone,

i have the following customer names and amounts:

A B
Customer A 99.00
Customer A 15.00
Customer A 45.00
Customer A -100.00
Customer B 5000.00
Customer B -25.00
Customer C 100.00
Customer C 123.00
Customer D 100.00

I want to remove the negative values from this list by subtracting them from the first positive values that appears for each customer until the negative value becomes zero.

If the absolute value of the negative amount is greater than the first positive value for the customer, then continuing subtracting in the next row for the same customer.

Do this for each customer. How can this be done?

Thanks everyone!

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

##### MrExcel MVP
If you have a list of distinct customers in D from D2 on downwards and the data is A2:B9...

In E2 enter & copy down:

=SUMIF(\$A\$2:\$A\$9,D2,\$B\$2:\$B\$9)

#### liaosy

##### Board Regular
keep list itemized and update

thanks for suggestion.

but i am trying to keep the list itemized but simply setting all negative field values to zero while subtracting the negative amounts from the postive ones (starting fro the top down per customer).

if i run a sumif it yields a single sum total whiel disregarding the itemization by row.

the end result i am trying to get is:

A B
Customer A 0
Customer A 45.00
Customer A 0
Customer B 12.00
Customer B 100.00
Customer B 0

where the zeroes represent all the previously negative values (last row of each cutomer) and the zeroes in the top rosw represent the postiive vales that were used to subtract the negative values.

does this make sense?

thanks a.

#### liaosy

##### Board Regular
next step?

ok - since there are no -takers - let me try to work through the next step...

if i insert an empty row between each unique customer group, i can work with the top and bottom values more easily.

there needs to be an if statement that says if the absolute value of the negative amount is > than the first positive value then add the first positive value to the next positive value in R[1]C AND set the first row value to zero.

a loop needs to run in case the absolute value of the last row/negative value is still greater than the first two rows / positive values...

a second loop needs to run for each item in the collection to remove the negative values from all customer rows.

can anyone help produce the code for this? thanks!

=)

Replies
2
Views
261
Replies
1
Views
140
Replies
1
Views
83
Replies
0
Views
264
Replies
9
Views
142