need a formula/function

[Dandapani S]

Hi all,
Good Morning! I need a formula/function to arrive the following solution.
Example:
Input:
Row1:
Value:
A:
B:
C:
D:
E:
Row2:
6602
1600
2705
5155
11970
3870
Required Output:
Row1:
A:
B:
C:
D:
E:
Row2:
1600
2705
2297
0
0
Based on value 6602....A,B,C should be required...if value exceeds, the rest of the coloumn output should be zero....6602=(1600+2705+2297)...pls help

 

[sudipto3003]

hi dandapaniS,
First welcome to Chandoo forum, your post is not clear better you attach a sample file with your post which will be easy for the members to understand your problem.

 

[Dandapani S]

Sorry for the inconvenience caused.....pls see the attched file...i need one value should be spread across columns based on "n" of data...

 

[sudipto3003]

ok, but in your sample file at the first row value is 6602, but in green column C you have put 2297, as per your requirement (1600+2705+5155)=9460 which is greater than 6602, so the result must be "0" at here. please clear your query.

 

[Somendra Misra]

@Dandapani S

In your uploaded file put below formula in Q7 and copy to U7 and down to Q9.
=IF(SUM(D7:$E7)>$C7,IF($C7<$E7,IF(COUNTIF($P77,">0")=1,0,$C7),0),IF(SUM($E7:E7)<$C7,E7,$C7-SUM(D7:$E7)))

Just advise if any issue.

Regards,

 

[Somendra Misra]

Modified formula because of smiley in last post.
=IF(SUM(D7:$E7)>$C7,IF($C7<$E7,IF(COUNTIF($P7:P7,">0")=1,0,$C7),0),IF(SUM($E7:E7)<$C7,E7,$C7-SUM(D7:$E7)))

regards,

 

[Dandapani S]

Thank you....but the formula is not working for third row...(Single value).

 

[Somendra Misra]

@Dandapani S

Please have a look of attached file, the formula is working for all rows.

Advise if any issue.

Regards,

 

[Dandapani S]

Sorry! It's my mistake....Thank you very much...

 

[Somendra Misra]

Welcome anytime.