If then add

[billandrew]

Good afternoon all

Dividing the below, when the result is greater than 8 add .5 to the divisor and then divide the dividend by the new divisor.

70.25	8.2	8.57
65.41	5.25	12.46
20.35	2.5	8.14

<colgroup><col width="70" style="width:53pt"> <col width="86" style="width:65pt"> <col width="96" style="width:72pt"> </colgroup><tbody>
</tbody>

 

[wideboydixon]

Lilke this?

Book1
	A	B	C
1	70.25	8.2	8.07
2	65.41	5.25	11.38
3	20.35	2.5	6.78
Sheet1

Cell Formulas
Range	Formula
C1	=IF(A1/B1>8,A1/(B1+0.5),A1/B1)

WBD

 

[billandrew]

That did what I asked.

However, in the second row example , is there a way to add whatever is needed to return 8. Similar to the goal seek function.

Thank You

 

[wideboydixon]

Book1
	A	B	C	D
1	70.25	8.2	7.63587	9.2
2	65.41	5.25	7.928485	8.25
3	20.35	2.5	6.783333	3
Sheet1

Cell Formulas
Range	Formula
C1	=IF(A1/B1>8,A1/(B1+ROUNDUP((A1/8-B1)/0.5,0)*0.5),A1/B1)
D1	=A1/C1

Showing results and actual divisor.

WBD

 

[billandrew]

Most likely did not explain correctly. I need the result to be 8 or less.

Th second row B2 would be 8.1762. Then when 65.41 is divided into this the result would be 8.

 

[wideboydixon]

But you can't get 8.1762 by incrementally adding 0.5 to the original value of 5.25!

Book1
	A	B	C	D
1	70.25	8.2	8	8.78125
2	65.41	5.25	8	8.17625
3	20.35	2.5	8	2.54375
Sheet1

Cell Formulas
Range	Formula
C1	=IF(A1/B1>8,8,A1/B1)
D1	=A1/C1

WBD

 

[billandrew]

Yes. Is there another option to obtain the number required.

 

[wideboydixon]

I have no idea what your requirements are any more. I thought I gave the answer in post #4 so you'll have to explain to me what's wrong with that and exactly what you want to achieve.

WBD

 

[billandrew]

I need the value in Column B to be whatever is required so the result would be no more than 8.

 

[wideboydixon]

But column B has original values. If you want to change them then you need VBA. Post #6 has a solution that doesn't need VBA if you're happy to copy the values from column D to column B.

Man! I hate these shifting requirements!

WBD