SOLVED

# Value Distribution based on Criteria

Steel Contributor

# Value Distribution based on Criteria

Hey guys,

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
24 Replies
best response confirmed by Rodrigo_ (Steel Contributor)
Solution

# Re: Value Distribution based on Criteria

See the attached demo workbook.

# Re: Value Distribution based on Criteria

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!

# Re: Value Distribution based on Criteria

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

# Re: Value Distribution based on Criteria

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

# Re: Value Distribution based on Criteria

Hi, Yes exactly I do need it

# Re: Value Distribution based on Criteria

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.

# Re: Value Distribution based on Criteria

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

# Re: Value Distribution based on Criteria

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

# Re: Value Distribution based on Criteria

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

# Re: Value Distribution based on Criteria

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.

# Re: Value Distribution based on Criteria

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

# Re: Value Distribution based on Criteria

See:

A Google search will turn up more options.

# Re: Value Distribution based on Criteria

@HansVogelaar 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

1 best response

Accepted Solutions
best response confirmed by Rodrigo_ (Steel Contributor)
Solution

# Re: Value Distribution based on Criteria

See the attached demo workbook.