Formula Help

[zone709]

Looking for a formula maybe in Column J that i can pull down and give me the results. Numbers in column D Higher then 8 need to break it out like i did in i and J like 8 and 2
Next number is 9 so would be 8 and 1. If any number is under 8. Then i need it to pull the number in and i and J can have a 0 or not like you see the 6 0. Any help much apricated

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2										Formula
3				10					8	2
4				9					8	1
5				11					8	3
6				11					8	3
7				6					6	0
8
9
Sheet1

 

[Joe4]

Like this for J3 (copied down)?

=MAX(D3-8,0)

If you also need a formula for column I, you could use:

=MIN(D3,8)

 

[Micron]

Won't max just use the maximum value in the whole column (11) rather than compare row by row?
I was also wondering about using Mod, but don't know if any value can exceed 15 because the sample data is so small. Something like
=IF(O6>8,MOD(O6,8),O6) to get the remainder, and
=IF(O6>8,O6-Q6,O6) to get the major value
but you can see it doesn't work for 16 and up.

 

[zone709]

I will try with Joe4 first as its working. My number usually doesn't go higher then 15 but sometimes its possible.

 

[Micron]

Never mind. I missed that D3 would become D4 and so on. Not sure if it works for >15 since I don't know about that.
EDIT - FYI, I posted before seeing your latest post.

 

[Cubist]

If you used this in col I

=MIN(D3,8)

then in J you don't need the MAX() since it will never go above what's in I.

=D3-I3