formula for proportionating the amount

[anwitha]

Hi,

I have applied the formula for calcuating proportionate amount that needs to be porportionated between fruits and vegitables, but for few cases its not giving the desired result (which is highlighted in the sample sheet). Column J (manual result) should match with Colomum E (formula cell). Pls help.

[vlady]

i think this part is enough for that
=C3*IFERROR((SUM(L3:P3)/(SUM(L3:P3)+SUM(R3:AF3))),1)

this part
=IF(SUM(L3:P3)=0,0,... return 0 when theres nothing to add those cell disregarding the r3 to af3 and also the value in c3

[anwitha]

Thanks Vlady. Its working fine..but I have one more similar issue with proportinating the amount.Here in this sample sheet i need to get the answer in formula column (manual answer given in next colum)but this formula doesn't work for few cases. Pls hlep.

Thanks.

[vlady]

try
J4=IF(C4=0,B4+D4,B4+E4)
N4=IF(B4=0,C4+F4,C4+D4)

[anwitha]

Hi Valsy,
Thanks for ur help, but for last three line items its not giving the exact reslut in (in J4=IF(C4=0,B4+D4,B4+E4 forumla). Pls check in the sheet attaached earlier and do needful..

Thanks.

[vlady]

maybe this one. I think I'm lost.

=IF(F4>0,B4+E4,IF(C4=0,B4+D4))

what if column B or C has value?

[anwitha]

along with formula you given (=IF(F4>0,B4+E4,IF(C4=0,B4+D4), if B has a value then it shouldbe B+D, and if C has value then it should be C+F

[vlady]

so
=IF(B4>0,B4+D4,E4)
and
=IF(C4>0,C4+F4,F4)