Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Using a condition for fixed addition formula in desired cell

0

Hello Respected Experts,

The query is quite funny and complex but I have been asked by my director to resolve it by any means on URGENT BASIS.

In the attached excel:

Allowed Leave: 2 (Every month 2 leaves will be given)

Leave Taken for Jan: 3

Balance for Jan: -1

Hence, Simple Maths: Opening Balance for Feb should be (-1+2=1)

But my boss wants condition in which Feb month opening balance should be 2 even if previous balance is in negative (-1 for Jan). Hence, J5 should be 2. How can this be possible?

Please guide and help me ASAP.

Thanks and Regards,

Akash Sharma

Answer
Discuss

Answers

1

Terminology! Each month has a 'balance' of 2 - the Allowable amount. However, the Cumulative balance should and does consider the prior month's true balance.  Show a line for 'Allowable' and a line for Actual Balance.

Discuss

Discussion

Hi,
Still unclear from your response, I am confused, May you please demonstrate in excel file shared earlier.
Regards,
Akash Sharma
Akash Sharma (rep: 40) Jan 30, '19 at 9:48 am
Add to Discussion
0

If the permissible leave is 2 days at the beginning of each month then the "2" isn't the result of any calculation. It's just 2.

If the permissible leave at the beginning of each month us subject to some calculation then the only thing you have told us about that is that it is not carrying forward a previous balance unconditionally. That isn't enough information.

If there is some complicated calculation by which to determine the balance at the beginning of each month it's highly likely that your sample worksheet isn't suitable to demonstrate a solution because it only covers one month. Whatever might work for Feb doesn't necessarily also work for Mar, and that is provided that the output will indeed be a single row for a year - and how to deal with the beginning of the next year?

Discuss

Discussion

Hi Variatus,
Is there a condition to get the answer as 2 in J5?
In following months, the balance can be more than 2, thats okay, only concern is that if previous balance is (-1) and when we want to add 2 to it, answer should result as 2 not 1 in J5, can it be possible?

Thanks and Regards,
Akash Sharma 
Akash Sharma (rep: 40) Jan 29, '19 at 5:28 am
Both your sample and your definition are inadequate. @Queue's suggestion below may show the way. Extend your sample to include several months. I think you want Allowed Leave to be 2 for every month, Leave Taken to be actual and Balance to be cumulative. So, for February the guy get 2 days allowed leave. If he takes 1 day actual leave the cumulative balance for Feb will be zero. If this is what you want the Allowed Leave would be 2 for every month and your question would be how to calculate the balance.
Variatus (rep: 4889) Jan 29, '19 at 9:14 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login