check for condition"Number or not" and subtract

[kannanr03]

Hi all,

It would be better, if you can open my attached file before you deal with the problem.

Ignore the INPUT set of cells. My cells of issues are only with OUTPUT cells and the BALANCE TRACKER table. For the present scenario, consider project A being allotted for person X only in the all the three months. In this case, I can find the balance and keep a track of it in the TRACKER table. But, the real problem happens when I allot some other projects say, B or C in the month of Jan or Feb. In such a case, If the project A is allotted in any one of the previous months how can I find the balance from the balance of that allotted month?. Also, if the project A is not allotted in any of the months, how can I find the balance from the original budget amount of that project.

I have tried to put my words in the form a logic below.

Problem to be solved for: Cell R8

Requirement: Simple logic to replace------> IF(ISNUMBER(LOOKP(R8, L19:O21,3,FALSE),VLOOKUP(R8,L19:O21,3,FALSE)-S8, IF(ISNUMBER(LOOKUP(R8,L19:O21,2,FALSE),VLOOKUP(R8,L19:O21,2,FALSE)-S8, VLOOKUP(R8,A8:B11,2,FALSE)-S8)))

Need for a simple logic: I find this logic getting more complex when I use it for the next upcoming months and I cannot imagine how it would be for the month of december!!

So, please help me with a simple logic which I can use it for all the months. If you got any difficulty in understanding my problem, please do let me know.

Thanks in advance

[kannanr03]

If something is not clear, please do let me know. Im waiting for a solution from any one.. #fingerscrossed

[ron2k_1]

I really gave it a look. And pretty much everything is unclear. So this is what I follow so far:
1. Your budget for A, is 100
2. Person X was allocated to Project A in the three months
3. Because the salary of person x is 10, it means that in each month 10 is used from the budget
4. That is why at the end of January, for project A, you had a balance of 90; and because you've used person x on each month for project A, you've depleted 30 (10 x 3 months) from your budget and that is why you only have 70 at the end of March

Now, tell us what the issue is.
1. Explain a scenario (e.g. if you plug A (or B, or C) in cell F9, and drag down the formula, etc).
2. What the expected result should be. We don't need to know what it does now (we can find that out ourselves)
3. What difficulties are found ahead. Provide what scenarios we may encounter given you current layout and formulas.

[Ace_XL]

Couldn't you just use

=VLOOKUP(Q8,I19:M21,3,FALSE) and replace the month no. for each month ?

[kannanr03]

@ron2k_1

You were right with all the 4 points which you have followed.

What I expect is: I must be able to find the find the balance of each month and display it individually in the tracker table. This calculation of balance must be possible in all the following scenarios:

1. consider only project A being allocated to the person X for all the 3 months. In this case, the balance of Jan must be deduced from the total budget using the salary of 10 (which is constant for person X). Then, balance of Feb must be deduced from the latest balance of Jan and similarly for March it must be done from the latest balance of Feb.

2. In another scenario, If some other project say B or C allocated to person X, then the balance cell G8 will display #N/A. In this case, the balance of Feb must be deduced from the total budget and for March from the balance of Feb.

3. In another scenario, If project A is allocated in Jan and March, but not allocated in Feb. In this case, the balance of Jan must be deduced from the total budget and for March from the balance of January.

4. In a scenario, where no allocation of Project A is done in Jan and Feb, but allocated to March, the balance of March must be deduced from the total budget of Project A.

Note: Though Project A is not allocated to any one of the months, I must be see the latest balance of Project A in the tracker table.

Hope I explained my needs. Still you need any further clarification, please do let me know.
Thanks in advance

---------- Post added at 07:13 PM ---------- Previous post was at 07:08 PM ----------

@Ace_XL,

Your suggestion does not fit for all the scenarios. For eg., If I do not select Project A for the month of Jan, the balance of Feb & March will become incorrect., say Jan & Feb displays #N/A and March displays a balance of 70 which is not true. Instead the balance of March must show 90 (which is the total budget(100)-salary(10) = 90). Hope you I made you clear now. Looking forward for your suggestion on this.

Thank you

[kannanr03]

@ron2k_1 & @Ace_XL and others, I wish someone got any clue on this. Looking forward to know.

[ron2k_1]

Hi kannanr03,

sorry I didn't get back to you earlier. I almost forgot about you.

Try this and then tell me if it works for you as I don't really have the time to test it:
On the tracker window, change your in J19, K19, L19, respectively to [Array formulas, so confirm with Ctrl+Shift+Enter]:
=MIN(IF(($F$8:$F$12=$I19)*($G$8:$G$12<>""),$G$8:$G$12,B9))
=MIN(IF(($L$8:$L$12=$I19)*($M$8:$M$12<>""),$M$8:$M$12,J19))
=MIN(IF(($Q$8:$Q$12=$I19)*($R$8:$R$12<>""),$R$8:$R$12,K19))
Notice that in all 3 cases it is the same formula and I'm just changing the references to the different month tables, you can continue doing the same for the months after that [April, May...], just copy the formula and reference the ranges to the location of the new month. You can drag down the formula as well in case you want to track the other projects B and C (You can place the letter B in I20 and C in I21).

Then, for Feb and Mar project tables you are telling the IF formula [in M8] to do the samething in both cases where the test is true and false. Your formula is:
=IF(ISNUMBER(VLOOKUP(L8,I19:L21,2,FALSE)),VLOOKUP(L8,I19:L21,2,FALSE)-N8,VLOOKUP(L8,I19:L21,2,FALSE)-N8)
Just change it to the following:
=IF(ISNUMBER(VLOOKUP(L8,$I$19:$L$21,2,FALSE)),VLOOKUP(L8,$I$19:$L$21,2,FALSE)-N8,0)
And don't forget to change R8 to:
=IF(ISNUMBER(VLOOKUP(Q8,$I$19:$L$21,3,FALSE)),VLOOKUP(Q8,$I$19:$L$21,3,FALSE)-S8,0)

I'm working with your formulas and didn't get a chance to test all your scenarios, so kindly do that and I'll check this later on today or tomorrow morning

[kannanr03]

Hi ron2k_1,

Your formulas works good for one person say., Person X and if I extend the same idea to the next person "Y", everything messes up. I have implemented your formulas on the sheet1. And in sheet2, I have filled the sheet with my requirement in which the balance of previous allocated person in the previous month has to be used to find the balance. The tracker window must therefore display the latest balance in each month as shown in file.

Thanks for your efforts. Hope you could solve this out.

P.S: Please find the new file attached.

[ron2k_1]

I didn't follow what was on your first sheet of your attachment, so I'm going by what I think your aim is, cause if your aim is what you're showing me in the worksheet labeled "Required Output", then what I think I know of your requirements is completely off. So try this and see if it helps:

Change your formulas in the following cells:
G8=INDEX($B$9:$B$11,MATCH(F8,$A$9:$A$11))-SUMIF(F$8:F8,F8,H$8:H8)
M8=IF(ISNUMBER(VLOOKUP(L8,$I$19:$L$21,2,FALSE)),VLOOKUP(L8,$I$19:$L$21,2,FALSE)-SUMIF(L$8:L8,L8,N$8:N8),0)
R8=IF(ISNUMBER(VLOOKUP(Q8,$I$19:$L$21,3,FALSE)),VLOOKUP(Q8,$I$19:$L$21,3,FALSE)-SUMIF(Q$8:Q8,Q8,S$8:S8),0)

They are not arrays so just press Enter. If you noticed I only added a SUMIF to your formula.

Hope this helps,

[kannanr03]

Nope. I tried this code previously. But did not helped. As my requirement is, For example, If project A allocated to Person Y, the balance must be deduced from the March balance in which Project A has been allocated to Person X after which the balance is 70 and so the result in G8 must be 50 and not 80 which actually deduces from the original total budget cell B9.

This is the same I have mentioned in the "Required Output" sheet in my file. Please have a look at it again. hope you will get it this time

[ron2k_1]

I really want to help you on this, but I cannot follow the requirements are. This can't be this hard, but sorry I'm really having a hard time in following with the info you provided. So lets do this:

1. Modify your attachment in post #8 .
2. Insert notes in each of the cells in the worksheet "Required Output" showing us how you arrived at the different calculations. Say something like "Balance of 90 from G8, Less N8, Less S8", or "Balance of 90 from Balance Tracker Jan, Less N8, Less S8"...
3. What happens when you introduced another month, April, May, June... etc. Say how these will affect the balance in January, February, etc.