Share via

Subtract until cell reaches zero before moving on

Anonymous
2012-01-24T17:14:41+00:00

Hello,

I am trying to create a spreadsheet to track employees annual leave. There are different types of leave with different expiry dates; I've called them Type A and Type B here. 

As employees take holidays I would like the days of holiday to be subtracted first of all from the column which is currently calculating the number of days of Type A leave that they have. If the employee is claiming more holiday days than they have Type A leave then once that total reaches zero I would like excel to move onto the next column and continue subtracting from there.

Doing this will ensure that when employees claim holiday days they will end up using up the leave they have accumulated which needs to be spent first.

I would be grateful to anyone who would be able to shed light on this and I'll do my best to provide more information if required. I've outlined an example below.

Sincerely,

Roldris

Example...

Joe Bloggs has

Leave (Type A): 8 Days

Leave (Type B): 8 days

when Joe claims 10 days of holidays he should be left with

Leave (Type A): 0 Days

Leave (Type B): 6 Days

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2012-01-25T00:31:56+00:00

    Hi,

    Try this

    =MAX(0,B2-C2)

    B2 has 8 i.e. Leave (Type A)

    C2 has 10

    1 person found this answer helpful.
    0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2015-12-13T03:24:52+00:00

    Hi,

    Try this

    1. In cell D2, enter this formula

    =MAX(0,C2-E$2)

    1. In cell D3, enter this formnula

    =C3-(E$2-SUM(C$2:C2))

    Hope this helps.

    0 comments
  2. Anonymous
    2015-12-12T13:09:04+00:00

    Dear Ashish ji,

    Please consider following image:

    I need to do subtraction column wise. For example, if I pay money in E column say E2, the total payable C column should effect. Say C2 is 10,000/- and I paid 15000 in E2. The C2 column should be zero ("0") and C3 should be 20,000/-.

    Is the any formula in excel which can help me. It should deducted the same cell until it becomes "0" (Zero), once it is Zero, it should jump to next COLUMN and do the same function. 

    Kindly help.

    Regards

    Swami Kastur

    0 comments
  3. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2012-01-30T23:19:51+00:00

    You are welcome.  Glad to help.

    0 comments
  4. Anonymous
    2012-01-30T22:51:18+00:00

    Dear Ashish,

    Many thanks for your reply, this answer has been very helpful. Using the MAX function was useful here and for some more complicated calculations I needed to do too!

    Kind Regards,

    Graham

    0 comments