Multiple function difficult formula (array, sumif, if, etc)

[SolCo]

I'm looking for a formula (or two), that will allow me to do the following:

For any given project code (column A), if the sum of the ON Shore weights is equal to zero (Column C), then apportion those costs (Column D) which are associated to all the On shore rows (for that given project code) equally across all the Off Shore rows (for that given project code).

And Vice versa

i.e. for any given project code (column A), if the sum of the OFF Shore weights is equal to zero Column C), then apportion those costs (Column D) which are associated to all the OFF shore rows (for that given project code) equally across all the ON Shore rows (for that given project code).

And if both the sum of the On Shore and OFF Shore is not equal to 0, then leave the costs column (D) as it currently is.


I'm having serious trouble with this, if anyone could help me with this, it would be greatly appreciated.
Thanks!!!

PROJECT_ID SHORE WEIGHT Costs Weighted Costs a On Shore 0.1 123 a On Shore 0.1 234 a On Shore 0.8 345 b On Shore 0 543 b On Shore 0.67 432 b On Shore 0.33 527 b On Shore 0 571 c On Shore 1 614 d On Shore 1 658 e Off Shore 0.5 701 e Off Shore 0 745 e Off Shore 0.5 788 e Off Shore 0 832 f Off Shore 1 875 g Off Shore 0 919 g Off Shore 1 962 h Off Shore 1 1,006 i On Shore 0.39 1,049 i On Shore 0.61 765 j Off Shore 1 64 k Off Shore 0 53 k Off Shore 1 576 l On Shore 1 68 <colgroup><col><col><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[J.Ty.]

I do not understand. In your example no project code has simultaneously "On Shore" and "Off Shore" costs, hence it seems to me that in this example there is nothing to do.

Please fill column E with the results you think should be there.

J.Ty.

 

[mikerickson]

"if the sum of the ON Shore weights (for a project) is equal to zero (Column C), then "
In the sample data, that situation never obtains.

 

[SolCo]

I do not understand. In your example no project code has simultaneously "On Shore" and "Off Shore" costs, hence it seems to me that in this example there is nothing to do.

Please fill column E with the results you think should be there.

J.Ty.

Hi TY thanks for picking this up..
True, in this sample there isn't that scenario but in the 100's of real data lines there is.
There are many instances of cases where a given project code has simultaneous on shore and off shore costs.

Really appreciate any help with this.

Thanks

 

[SolCo]

hope I've made the example clear, happy to answer any questoins you have on this

 

[SolCo]

"if the sum of the ON Shore weights (for a project) is equal to zero (Column C), then "
In the sample data, that situation never obtains.

Hi Mike thanks for picking this up..
True, in this sample there isn't that scenario but in the 100's of real data lines there is.
There are many instances of cases where a given project code has simultaneous on shore and off shore costs.

Really appreciate any help with this.

Thanks

 

[J.Ty.]

Dear SolCo,

Please post a sample of your data where there is something to compute and with values in column E (you can compute them manually) showing the expected results.
We will then have a test case to see if our solutions work.

J.Ty.

 

[SolCo]

PROJECT_ID SHORE WEIGHT Costs Weighted Costs a On Shore 0.1 123 a On Shore 0.1 234 a Off Shore 0.8 345 b On Shore 0 543 b On Shore 0.67 432 b Off Shore 0.33 527 b On Shore 0 571 This 571 (D9) needs to be multiplied by 0.66 and added to E8 c On Shore 1 614 d On Shore 1 658 e Off Shore 0.5 701 e On Shore 0 745 e Off Shore 0.5 788 e Off Shore 0 832 f Off Shore 1 875 g On Shore 0 919 g Off Shore 1 962 h Off Shore 1 1,006 i On Shore 0.39 1,049 i On Shore 0.61 765 j Off Shore 1 64 k Off Shore 0 53 k On Shore 1 576 l On Shore 1 68 <colgroup><col><col><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[SolCo]

PROJECT_ID SHORE WEIGHT Costs Weighted Costs a On Shore 0.1 123 a On Shore 0.1 123 a Off Shore 0.8 123 b On Shore 0 543 b On Shore 0.67 543 b Off Shore 0.33 543 b On Shore 0 543 c On Shore 1 614 d On Shore 1 658 e Off Shore 0.5 701 e On Shore 0 701 This 701 needs to be apportioned equally to the other OFF shore costs for this project code (D 12, 14 and 15), because the sum of the weights for the ON Shore part of this project code equals zero. e Off Shore 0.5 701 e Off Shore 0 701 f Off Shore 1 875 g On Shore 0 919 g Off Shore 1 919 h Off Shore 1 1,006 i On Shore 0.39 1,049 i On Shore 0.61 1,049 j Off Shore 1 64 k Off Shore 0 53 k On Shore 1 53 l On Shore 1 68 <tbody> </tbody>

<tbody>
</tbody>

Much better example .... hope this is clearer please ignore the previous post #8, it was plain wrong.

 

[J.Ty.]

What version of Excel do you use?

J.Ty.