remove / calculate negative values

liaosy

Board Regular
Joined
Apr 27, 2005
Messages
77
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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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)
 
Upvote 0
keep list itemized and update

hi aladin,

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.
 
Upvote 0
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!

=)
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top