Value Distribution based on Criteria

[Rr_]

Hey guys,

Need your help!
My problem is how am I gonna distribute my remaining values based on my total output. then if the remaining value is 0 and my total output does not meet it yet, it will continue on its next cell.
B1:F:1 is my remaining Values
G3:G11 is my Total output
B3:F11 is where I need to put your formula.

Please refer on my table below:

	A	B	C	D	E	F	G
1		41.5	38.5	96.5	42.5	125.5
2		G056	I076	K090	H060	M113	OUTPUT
3	1-Dec						43
4	2-Dec						42
5	3-Dec						44
6	4-Dec						49
7	5-Dec						46
8	6-Dec						40
9	7-Dec						0
10	8-Dec						0
11	9-Dec						0
12	TOTAL:	0	0	0	0	0	264

this is my goal inside my table:

	A	B	C	D	E	F	G
1		0	0	0	0	80.5
2		G056	I076	K090	H060	M113	OUTPUT
3	1-Dec	41.5	1.5				43
4	2-Dec		37	5			42
5	3-Dec			44			44
6	4-Dec			47.5	1.5		49
7	5-Dec				41	5	46
8	6-Dec					40	40
9	7-Dec						0
10	8-Dec						0
11	9-Dec						0
12	TOTAL:	41.5	38.5	96.5	42.5	45	264

[#2 · Accepted solution]

@Rr_ 

See the attached demo workbook.

[#3]

Wow! you got it man! SUPER THANKS!
I didn't expect that you could use the MIN function on my problem, you made it look soooo easy man. It's so cool and funny but at the same time I feel embarrassed! hahaha!

[#4]

@Hans Vogelaar 

Could you please on below start and end date automate calculation. 

[#5]

@TharakaMT 

Do you mean that you want formulas in columns BN and BR that calculate the dates based on columns CK to CP?

[#6]

Hi, Yes exactly I do need it

[#7]

@TharakaMT 

In BN3:

=INDEX($2:$2, MIN(IF($CK3:$CP3<>0, COLUMN($CK3:$CP3))))

In BR3:

=INDEX($2:$2, MAX(IF($CK3:$CP3<>0, COLUMN($CK3:$CP3))))

If you do not have Microsoft 365 or Office 2021, confirm the formulas by pressing Ctrl+Shift+Enter.

Fill down.

[#8]

Woow you are superb. This will make more easy to do my daily work. Thank you so much great man.

[#9]

@TharakaMT  I used your given formulas into this excel sheet, but still have some errors 

https://docs.google.com/spreadsheets/d/1cpY83u22isAS3PjXnM8KTGLY6XLgjKaS/edit#gid=1639334711

[#10]

I used your given formulas into this excel sheet, but still have some errors

https://docs.google.com/spreadsheets/d/1cpY83u22isAS3PjXnM8KTGLY6XLgjKaS/edit#gid=1639334711

[#11]

@TharakaMT 

I get "Access Denied". Please share the workbook, then get the link and paste it into a reply.

[#12]

https://docs.google.com/spreadsheets/d/1cpY83u22isAS3PjXnM8KTGLY6XLgjKaS/edit#gid=1639334711

[#13]

https://docs.google.com/spreadsheets/d/1cpY83u22isAS3PjXnM8KTGLY6XLgjKaS/edit#gid=1639334711

[#14]

Could you please help to check this

[#15]

could you please check now

[#16]

@TharakaMT 

Sorry for the delay. I hadn't received a notification of your earlier replies.

Column CC contains formulas that refer to a range that doesn't exist.

The same goes for FA13:FB16.

Hence the #REF! results.

I have tried to correct the formulas, but please check carefully whether the formulas are now OK - I cannot judge that.

[#17]

@Hans Vogelaar  you are really helpful & do not know how to thank you. I need to learn advanced excel. How can I learn ? 

[#18]

@TharakaMT 

See:

Learn Excel (there are links to tutorials about more advanced subjects)

A Google search will turn up more options.

[#19]

Thanks, i will try to learn more.

[#20]

@Hans Vogelaar This file has more formulas and below msg shows every time. I tried to check any 

I checked any cells as below & could not find. Also the file is too slow when going to update. Please kindly help me to solve these problems. Thanks