Hi all,
I am trying to do the following:
- I have a list of items (say cars), one on each line, and attached there is the name of the owner and the value of the car
- I need to pick up the first n most valuable cars for each owner up to a value of x
- every car that makes me exceed the total value of x has not to be picked up
My attempt:
- List the cars from the most expensive to the cheapest
- For each line sum the value of the previous cars belonging to the same owner
- So you have a cumulative sum *for each owner*
- In a second column, I flag the lines in which the value exceeds x
Seems simple but excel experiences huge difficulties in calculating the cumulative sum, probably because I am working on more than 20k lines.
It takes ages to calculate and I never managed to paste values to save my work.
I tried to obtain this result using a sumproduct [=SUMPRODUCT((D13=D13:$D$13)*(AB13:$AB$13))] where column D is the name of the owner and column AB is the value of the car.
I tried to do the same using sumif, but seems even worse.
I am running excel 64bit on a i7 with 8gb RAM, so the HW/resources should not be the problem.
Is there a smarter way to do this without using VBA?
Hope you can help me because this issue is really blocking me....
Thanks a lot
C
I am trying to do the following:
- I have a list of items (say cars), one on each line, and attached there is the name of the owner and the value of the car
- I need to pick up the first n most valuable cars for each owner up to a value of x
- every car that makes me exceed the total value of x has not to be picked up
My attempt:
- List the cars from the most expensive to the cheapest
- For each line sum the value of the previous cars belonging to the same owner
- So you have a cumulative sum *for each owner*
- In a second column, I flag the lines in which the value exceeds x
Seems simple but excel experiences huge difficulties in calculating the cumulative sum, probably because I am working on more than 20k lines.
It takes ages to calculate and I never managed to paste values to save my work.
I tried to obtain this result using a sumproduct [=SUMPRODUCT((D13=D13:$D$13)*(AB13:$AB$13))] where column D is the name of the owner and column AB is the value of the car.
I tried to do the same using sumif, but seems even worse.
I am running excel 64bit on a i7 with 8gb RAM, so the HW/resources should not be the problem.
Is there a smarter way to do this without using VBA?
Hope you can help me because this issue is really blocking me....
Thanks a lot
C
