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]

Professional Plus 2012

 

[J.Ty.]

Hi,

If you are using Excel 2007 or later, the following should work for you. Please copy the formulas from columns G, H, I, J down as far as necessary. Column G gives the amount resulting from repartitioning. Note that it reports more cases of repartitioning than what you have indicated, so please check it very carefully if my solution is OK.

J.Ty.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	PROJECT_ID	SHORE	WEIGHT	Costs	Weighted Costs		To be added	help1	help2	help3
2	a	On Shore	0.1	123			0	123	0.8	2
3	a	On Shore	0.1	123			0	123	0.8	2
4	a	Off Shore	0.8	123			0	246	0.2	1
5	b	On Shore	0	543			0	543	0.33	3
6	b	On Shore	0.67	543			0	543	0.33	3
7	b	Off Shore	0.33	543			0	1629	0.67	1
8	b	On Shore	0	543			0	543	0.33	3
9	c	On Shore	1	614			0	0	0	1
10	d	On Shore	1	658			0	0	0	1
11	e	Off Shore	0.5	701			233.6667	701	0	3
12	e	On Shore	0	701			0	2103	1	1
13	e	Off Shore	0.5	701			233.6667	701	0	3
14	e	Off Shore	0	701			233.6667	701	0	3
15	f	Off Shore	1	875			0	0	0	1
16	g	On Shore	0	919			0	919	1	1
17	g	Off Shore	1	919			919	919	0	1
18	h	Off Shore	1	1,006			0	0	0	1
19	i	On Shore	0.39	1,049			0	0	0	2
20	i	On Shore	0.61	1,049			0	0	0	2
21	j	Off Shore	1	64			0	0	0	1
22	k	Off Shore	0	53			0	53	1	1
23	k	On Shore	1	53			53	53	0	1
24	l	On Shore	1	68			0	0	0	1
Sheet1

 

[SolCo]

Thanks for this solution, I'm not sure I understand it just yet...

Does this solution result in the 701 from D12, being equally apportioned between D11, D13 and D14?
Also, does this solution take care of the vice versa scenario? ie. when the ON's and OFF's are reversed


Thanks so much for your help!!

 

[J.Ty.]

Does this solution result in the 701 from D12, being equally apportioned between D11, D13 and D14?

Yes, this amount 701 divided into 3 (i.e., 233.6667) is reported as "To be added" is rows 11, 13 and 14.

Also, does this solution take care of the vice versa scenario? ie. when the ON's and OFF's are reversed

Yes, this happens in rows 22 and 23 (project k), where Off Shore cost is reported as "To be added" to On Shore cost.


Best,

J.Ty.

 

[SolCo]

oooh I think you've got it!!

I'm gonna run it through my sheet- but fantastic stuff !! Great work and always really appreciated!

Best regards - if I need to, I hope its ok to come back to you further on this if I can;'t make it work.

Soli

 

[J.Ty.]

Dear Soli,

Thanks for the feedback and good luck!
Don't forget to exchange ";" into "," in all my formulas, as my signature reminds.
Of course, you can always send me a private message or reply to this thread - I'll keep my subscription to it.

Best,

J.Ty.

 

[SolCo]

Dear Soli,

Thanks for the feedback and good luck!
Don't forget to exchange ";" into "," in all my formulas, as my signature reminds.
Of course, you can always send me a private message or reply to this thread - I'll keep my subscription to it.

Best,

J.Ty.

Hi J.Ty.

Wanted to know what your formula is looking to do (if anything) when any given project only has ON shore or only has OFF shore, but not both. I think it's programmed to do nothing at the moment. But please could you confirm that.

If it is programmed to do nothing, then I believe I'll need some further help with this..

 

[J.Ty.]

Indeed, it does nothing when there is only one kind of costs.
Strictly speaking:

1) In a line with ON shore it looks for all OFF shore costs from the same project and verifies if there is anything that should be added to it
2) In a line with OFF shore it looks for all ON shore costs from the same project and verifies if there is anything that should be added to it

J.Ty.

 

[SolCo]

Indeed, it does nothing when there is only one kind of costs.
Strictly speaking:

1) In a line with ON shore it looks for all OFF shore costs from the same project and verifies if there is anything that should be added to it
2) In a line with OFF shore it looks for all ON shore costs from the same project and verifies if there is anything that should be added to it

J.Ty.

Ok thanks, as I thought ( I wasn't sure of what the "&" was in the formula.

Please would you be able to help me with the following additional criteria..

For any given project code, where there is both On and an Off shore presence, then multiply the On shore total cost by 0.66 and multiply the Off shore total cost by 0.33. If there is only OFF shore or only On shore then, the total is multiplied by 1 - i.e. no action.

Hopefully that's not too complicated, but I'm not sure how to build that formula either.

Thanks J.Ty. really appreciate it

 

[J.Ty.]

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.

Excel Workbook
	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
Sheet1