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

SolCo

New Member
Joined
Jul 11, 2011
Messages
42
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_IDSHOREWEIGHT Costs Weighted Costs
aOn Shore0.1 123
aOn Shore0.1 234
aOn Shore0.8 345
bOn Shore0 543
bOn Shore0.67 432
bOn Shore0.33 527
bOn Shore0 571
cOn Shore1 614
dOn Shore1 658
eOff Shore0.5 701
eOff Shore0 745
eOff Shore0.5 788
eOff Shore0 832
fOff Shore1 875
gOff Shore0 919
gOff Shore1 962
hOff Shore1 1,006
iOn Shore0.39 1,049
iOn Shore0.61 765
jOff Shore1 64
kOff Shore0 53
kOff Shore1 576
lOn Shore1 68

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,902
"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

New Member
Joined
Jul 11, 2011
Messages
42
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

New Member
Joined
Jul 11, 2011
Messages
42

ADVERTISEMENT

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

SolCo

New Member
Joined
Jul 11, 2011
Messages
42
"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.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web

ADVERTISEMENT

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

New Member
Joined
Jul 11, 2011
Messages
42
PROJECT_IDSHOREWEIGHT Costs Weighted Costs
aOn Shore0.1 123
aOn Shore0.1 234
aOff Shore0.8 345
bOn Shore0 543
bOn Shore0.67 432
bOff Shore0.33 527
bOn Shore0 571This 571 (D9) needs to be multiplied by 0.66 and added to E8
cOn Shore1 614
dOn Shore1 658
eOff Shore0.5 701
eOn Shore0 745
eOff Shore0.5 788
eOff Shore0 832
fOff Shore1 875
gOn Shore0 919
gOff Shore1 962
hOff Shore1 1,006
iOn Shore0.39 1,049
iOn Shore0.61 765
jOff Shore1 64
kOff Shore0 53
kOn Shore1 576
lOn Shore1 68

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>
 

SolCo

New Member
Joined
Jul 11, 2011
Messages
42
PROJECT_IDSHOREWEIGHT Costs Weighted Costs
aOn Shore0.1 123
aOn Shore0.1 123
aOff Shore0.8 123
bOn Shore0 543
bOn Shore0.67 543
bOff Shore0.33 543
bOn Shore0 543
cOn Shore1 614
dOn Shore1 658
eOff Shore0.5 701
eOn Shore0 701This 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.
eOff Shore0.5 701
eOff Shore0 701
fOff Shore1 875
gOn Shore0 919
gOff Shore1 919
hOff Shore1 1,006
iOn Shore0.39 1,049
iOn Shore0.61 1,049
jOff Shore1 64
kOff Shore0 53
kOn Shore1 53
lOn Shore1 68

<tbody>
</tbody>

<tbody>
</tbody>

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

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
What version of Excel do you use?

J.Ty.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,416
Messages
5,596,010
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top