Calculating split automatically

[Bilingual]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Price[/TD]
[TD]Spit[/TD]
[TD]Split - manually calculated[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HOK[/TD]
[TD]56[/TD]
[TD]38[/TD]
[TD]38[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]REG[/TD]
[TD]56[/TD]
[TD]38[/TD]
[TD]38[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TRA[/TD]
[TD]56[/TD]
[TD]38[/TD]
[TD]9,5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TRA[/TD]
[TD][/TD]
[TD][/TD]
[TD]9,5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TRA[/TD]
[TD][/TD]
[TD][/TD]
[TD]9,5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TRA[/TD]
[TD][/TD]
[TD][/TD]
[TD]9,5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi everybody, hope you can help me, i have a Table with the 4 columns.
What i want to do is be able to automatically calculate the Split, from the condition that Split and Price is empty.
It then needs to divide the split on all empty cells, until next cell with value shows up, can anybody help?
Kind regards Daniel

 

[jproffer]

Your sample data is inconsistent. In the first 2 rows, you have 38 with all 3 cells filled (fair enough). But in the 3rd row, you have 9.5 with all 3 cells filled. PLease provide sample data again and how to calculate the split if filled...or if one is empty, or if both are empty.

 

[Bilingual]

Your sample data is inconsistent. In the first 2 rows, you have 38 with all 3 cells filled (fair enough). But in the 3rd row, you have 9.5 with all 3 cells filled. PLease provide sample data again and how to calculate the split if filled...or if one is empty, or if both are empty.

Hi jproffer, sorry you are right, i did not make myself clear.

What i want is that the split is divided on all the forthcoming empty cells, PLUS the first cell with values.


[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]ccount[/TD]
[TD]Price[/TD]
[TD]Spit[/TD]
[TD]Split - manually calculated[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HOK[/TD]
[TD]56[/TD]
[TD]38[/TD]
[TD]38[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]REG[/TD]
[TD]56[/TD]
[TD]38[/TD]
[TD]38[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TRA[/TD]
[TD]56[/TD]
[TD]38[/TD]
[TD]9,5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TRA[/TD]
[TD][/TD]
[TD][/TD]
[TD]9,5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TRA[/TD]
[TD][/TD]
[TD][/TD]
[TD]9,5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TRA[/TD]
[TD][/TD]
[TD][/TD]
[TD]9,5[/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Here's a possibility:

Book1
	A	B	C	D
1	Account	Price	Split	Split - manually calculated
2	HOK	56	38	38
3	REG	56	38	38
4	TRA	56	38	9.5
5	TRA			9.5
6	TRA			9.5
7	TRA			9.5
Sheet1

Cell Formulas
Range	Formula
D2	{=IFERROR(LOOKUP(2,1/($C$1:$C2<>""),$C$1:$C2)/(MATCH(FALSE,$A2:$A1000=$A2,0)+ROW($A2)-2-LOOKUP(2,1/($A$1:$A1<>$A2),ROW($A$1:$A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Bilingual]

Thanks for your, try,i really appreciate it, however i just found out that in some cases the ACCOUNT name wont change when the price is split, so can you make it conditional only with the price or split? - kind regards

 

[wideboydixon]

Book1
	A	B	C	D
1	Account	Price	Split	Split - manually calculated
2	HOK	56	38	38
3	REG	56	38	38
4	TRA	56	38	9.5
5	TRA			9.5
6	TRA			9.5
7	TRA			9.5
8			END
Sheet1

Cell Formulas
Range	Formula
D2	{=IFERROR(LOOKUP(2,1/($C$1:$C2<>""),$C$1:$C2)/(MATCH(FALSE,$C3:$C$1000="",0)+ROW($C3)-LOOKUP(2,1/($C$1:$C2<>""),ROW($C$1:$C2))-1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

You'll need to add a non-empty value at the end of the "Split" column (example above) in order to make this work.

WBD

 

[Bilingual]

Hi, thanks for your help, but im affraid i cant make it work, if you have the time, could you look at what i do wrong? - i have uploaded it to Dropbox:

https://www.dropbox.com/s/iljk3zgwpmi93af/Test.xlsx?dl=0

 

[wideboydixon]

You didn't use the formula from above; you changed the LOOKUP to VLOOKUP and omitted the IFERROR. If you copy the formula as provided rather than changing it, all should be well.

WBD

 

[Bilingual]

Im sorry, it was a language issue,, my Excel 2010 version is in Danish and i LOOKUP is translated as VLOOKUP in Danish, now it works like a charm , thanks a LOT