Systematic allocation of expense

[Friend1]

Hi Friends, I need help automating small manual tasks.

Scenario:
we have 6 projects out of 3 project reports that need some details working.

For example: Person A1 has 7 pay in one quarter. The total amount is 51,000 out of 31,987, which belongs to those 3 projects which need detailed reporting. assume cost is not allocated by the project. How should we find out which pay period belong to the which project


- I want to automate that system to auto-add the pay and tell me Project 1 takes Pay 1 & 2 for A1, Pay 3 & 4 for Project 2 and Pay 5 & 6 for Project 3

[AliGW]

Explain why project 2 should be Pay 2 & 3 - project 1 has already taken ALL of Pay 1 & 2 (6000 + 7000 = 13000).

Explain the logic - what is your manual calculation here?

[Friend1]

Sorry I mean this:
Project1 Project2 Project3
pay 1 &2 Pay 3&4 Pay 5&6

The logic is that the first project take the starting pay, and as soon as the first project basket fill it move the next project starts new basket

[AliGW]

Please update the sample workbook so that it is CORRECT.

[Friend1]

revised the sample file

[AliGW]

Why is Project 3 not Pay 4 & 5? Do funds not carry forward?

Your logic is currently thin - we need to understand exactly how this is working in your head (because we can't see inside your head).

[Friend1]

It can be any pay fill the project 3. whether it Pay1 &2 or Pay2 & 3 or Pay 4 &5

Currently I make the simple assumption that the first project takes the first pay, and as soon as the project cost is filled it will lock for project 1 cost and no other project can use that cost and move to other pay to fill the other project

[AliGW]

OK - it's not clear to me. Too much that is ambiguous. I'll leave it to someone else, because you are failing to provide the manual calculations that are in your head. I'm not a mind reader, sadly.

[Friend1]

actually there is no calculation. its just manually add starting pays when its sum come to the level of project cost stop there and allocate those pay to project and move to other project apply same procedure sum other pays and make the sum of project 2 and allocate those pay and so on

[AliGW]

That's an issue, then: Excel is not human (it's not even at AI level), so it requires clear LOGIC (rules) to be able to replicate what is going on inside your head. If there is no clear logic, then you can't 'programme' Excel to do this for you.

Maybe someone can come up with something, but it's not me. Sorry.

[Friend1]

Thanks dear. that I want to find out if it is possible or not with a new feature of O365. Honestly I am also confuse how to make it automate

[windknife]

I guess in H4 and copy down.

Please Login or Register  to view this content.

[Friend1]

Hi Windknife,

Wow, you did it, so it is possible. But I do not understand how you created this formula. Can you please teach me so I can create it in my actual file?

Because I feel this need some addon to use. please help

[Friend1]

To understand your formula, I took some training in LET, SCAN, and LAMBA.

After that, I applied your formula to my sheet, but it gave me #CALC! error, I do not understand why. Can you please have a look

[windknife]

You set wrong range. Try this in BA5 and copy down.

Please Login or Register  to view this content.

[Friend1]

Good morning dear, thanks you so much for helping out.

I will mark this Thread to Solve.

[windknife]

You are welcome.