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>

 

[SolCo]

I have computed the factors: 0.33, 0.66 or 1, as you defined them. I am not absolutely sure how to use them. Please have a look at it.

J.Ty.

Sheet1

*	A	B	C	D	E	F	G	H	I	J	K
1	PROJECT_ID	SHORE	WEIGHT	Costs	Weighted Costs	multiply factor	To be added	help1	help2	help3	help4
2	a	On Shore	0.1	123		0.66	0	123	0.8	2	1
3	a	On Shore	0.1	123		0.66	0	123	0.8	2	1
4	a	Off Shore	0.8	123		0.33	0	246	0.2	1	2

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F2 =IF(AND(B2="On Shore";J2>0);0.66;IF(AND(B2="Off Shore";K2>0);0.33;1)) G2 =IF(AND(I2=0;J2>0);H2/J2;0) H2 =SUMIFS(D:D;A:A;A2;B:B;"<>"&B2) I2 =SUMIFS(C:C;A:A;A2;B:B;"<>"&B2) J2 =COUNTIFS(A:A;A2;B:B;B2) K2 =COUNTIFS(A:A;A2;B:B;"<>"&B2) F3 =IF(AND(B3="On Shore";J3>0);0.66;IF(AND(B3="Off Shore";K3>0);0.33;1)) G3 =IF(AND(I3=0;J3>0);H3/J3;0) H3 =SUMIFS(D:D;A:A;A3;B:B;"<>"&B3) I3 =SUMIFS(C:C;A:A;A3;B:B;"<>"&B3) J3 =COUNTIFS(A:A;A3;B:B;B3) K3 =COUNTIFS(A:A;A3;B:B;"<>"&B3) F4 =IF(AND(B4="On Shore";J4>0);0.66;IF(AND(B4="Off Shore";K4>0);0.33;1)) G4 =IF(AND(I4=0;J4>0);H4/J4;0) H4 =SUMIFS(D:D;A:A;A4;B:B;"<>"&B4) I4 =SUMIFS(C:C;A:A;A4;B:B;"<>"&B4) J4 =COUNTIFS(A:A;A4;B:B;B4) K4 =COUNTIFS(A:A;A4;B:B;"<>"&B4) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Ok, wow - looks fantastic, I'm going to try it out and see how it works. You have the nack for this! Really appreciate your time and speedy responses!

All the best, Sol

 

[SolCo]

Hi J.Ty.

I see this formula takes care of the 1st problem the splitting out of the 0.66 and 0.33, but does it take care of the 2nd problem which was that if there was only On shore or only Off Shore for a given project code, then it shouldn't multiply by 0.66 or 0.33, it should just do nothing. But when I plug in your above formula, every single line is coming out as 0.33 or 0.66?

Please let me know what you think.

THANKS!

 

[J.Ty.]

Use only formulas from row 2, the rest results from copying. I have forgotten to restrict the formulas to be displayed.

J.Ty.

 

[J.Ty.]

On my copy I have factor 1 in row 15.

J.Ty.

 

[SolCo]

Ahaaaa

I think you've done it! I believe this is what I was after!

Great work my friend - I think its great.
(hopefully I won't have to trouble you again) but I think I can make it work now from here on in...

really appreciate it
Soli

 

[SolCo]

I've had a look at this again and I only find 1 or 2 instances where its picking up a "1" in column F, when there should be a lot more, (as there are more than 2 projects which only have one or the other (ON or OFF Shore) parts.

Not sure it's working properly?

Thanks - any thoughts?

 

[J.Ty.]

You are right - there is a mistake in the formula from F2. Replace it by

=IF(AND(B2="On Shore";K2>0);0.66;IF(AND(B2="Off Shore";K2>0);0.33;1))

and copy down.
Remember about my signature.
J.Ty.

 

[SolCo]

I've had a look at this again and I only find 1 or 2 instances where its picking up a "1" in column F, when there should be a lot more, (as there are more than 2 projects which only have one or the other (ON or OFF Shore) parts.

Not sure it's working properly?

Thanks - any thoughts?

 

[SolCo]

Ok, phew that looks great now.
I'm going to plug them all in now and hopefully we should be done.

Thanks!!!

 

[SolCo]

Hi J. Ty.

Plugged it in to the sheet, but still I'm not sure it's quite there yet. I'm getting mixed results. For example, when there is only 1 On shore and nothing else, it still shows a multiplier of 0.66 and not 1?

Any thoughts on this?

Thanks

S