Question re subtracting numbers based on priority established

tonic1

New Member
Joined
Nov 16, 2015
Messages
6
Hi Guys

I'm not too sure how to phrase this question so I'll just show you an example of what I have:

A B C D E F G H I
Priority 1 Priority 2 Priority 3 Total Claim To be subtracted Updated 1 Updated 2 Updated 3 Updated Claim
10 10 10 30(Sum A,B,C) 15 0 5 10 15
20 20 20 60(Sum A,B,C) 40 0 0 20 20

EDIT: I have just realized how hard this is to read on screen so I just attached a picture of what I mean. The yellow highlighted cells are the ones I need help formulating: http://imgur.com/a/jRnXG


Essentially I have a total claim, which is made up of a number of smaller claims which have a certain order of Priority.

Now I have a number to be subtracted from the total claim, but it has to be subtracted in the order of priorities stated. I then want an updated balance of all priorities and total claim.

Kind of like a waterfall, where I want to take all the money out of priority 1 first, before moving onto priority 2 etc etc.

Any help with this would be much appreciated!
 
Last edited:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Copy/paste sometimes doesn't quite come out as expected here :9 Is this more or less what your table looks like?
A​
B​
C​
D​
E​
F​
G​
H​
1​
priority1priority2priority3Tot claim to be sub'dUpdated1Updated2Updated3Updated claim
2​
10
10​
10​
15​
0​
5​
10​
15​
3​
20
20​
20​
40​
0​
0​
20​
20​

If so, can you explain what you want and how you arrive at that please?
 
Upvote 0
Hi FDibbins

Thank you so much for your reply!

Sorry it's a little hard for me to explain - basically every row is a different employee.

Each employee is entitled to a payout which is broken up into priorities (1,2 and 3). So if you add all the priorities 1-3 you will get the total amount entitled to that employee.

Now we have had something come up where a number is to be taken out of the employee entitlements. Ie. we are reducing the total employee entitlements, however, because it is split into 3 priority funds, we first want to reduce the priority fund 1 to 0, and before reducing the priority fund 2 to 0 and so on.

So in the 2nd row example, your total claim is 30 (made up of 10 in each priority fund).

We now have to subtract 15 from the total claim.

Since there is only 10 in the priority fund 1, we subtract the whole 10 -> now the updated Priority fund 1 shows 0

Then there is 5 left to subtract, we subtract that from priority fund 2 -> Now the updated Priority fund 2 shows 5

As there is nothing left to subtract, Priority fund 3 stays the same.

I hope that all makes sense.

Thank you so much for your help!

Copy/paste sometimes doesn't quite come out as expected here :9 Is this more or less what your table looks like?
A​
B​
C​
D​
E​
F​
G​
H​
1​
priority1priority2priority3Tot claim to be sub'dUpdated1Updated2Updated3Updated claim
2​
10
10​
10​
15​
0​
5​
10​
15​
3​
20
20​
20​
40​
0​
0​
20​
20​

<tbody>
</tbody>


If so, can you explain what you want and how you arrive at that please?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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