If less than, divide by...

[Bedford]

Attempting a calculation. A cell (B13) results in a whole number based on a formula. That resulting whole number is dynamic, example; 2179. Then 2179-180=1999, 180 is static. The result 1999, needs to be divided by 2 if <1350, divided by 3 if > 1351 but < 1650, divided by 4 if > 1651 but < 2000, divided by 5 if > 2001 but < 2500, divided by 6 if > 2501 but < 3000.
I'm hoping this can be done in a formula, and that someone can assist.
Thank you in advance.

 

[jasonb75]

Something like this? Anything greater than 3000 will not be divided.

=(B1-180)/LOOKUP(B1-180,{0,1351,1651,2001,2501,3001},{2,3,4,5,6,1})

I haven't allowed for negative values in B1-180 so they will currently give you an error.

 

[steve the fish]

What if over 3000? Your boundaries are unclear also. What should 1350 produce as neither is it less than 1350 nor over 1351?

 

[Bedford]

Indeed that formula did result in an error, not sure what you mean by not adding negative values in B1-180. How can I get the result of the calculation without the #N/A error.

 

[Bedford]

What if over 3000? Your boundaries are unclear also. What should 1350 produce as neither is it less than 1350 nor over 1351?

Sorry, there is no over 3000. 1350 should be less than 180, result 1170, then divide by two, result should be 585. If the length goes over 1350, by even 1mm, so 1351, it will divide by 3, until it reaches 1650 and so on.
Hope that make sense.

 

[jtakw]

Hi,

Not sure if you're saying use B12 Before or After the subtraction of 180 as a rule for the division, your OP and post #5 seems to contradict:
2 versions, see if you're getting the results you want:

Book3.xlsx
	B	C	D
13	2179	499.75	399.8
14	1000	410	410
15	3000	470	470
16	2000	455	455
17	1600	473.3333	473.3333
18	2500	464	464
19	1350	585	585
20	1351	585.5	390.3333
Sheet832

Cell Formulas
Range	Formula
C13:C20	C13	=(B13-180)/LOOKUP(B13-180,{0,1351,1651,2001,2501},{2,3,4,5,6})
D13:D20	D13	=(B13-180)/LOOKUP(B13,{0,1351,1651,2001,2501},{2,3,4,5,6})

 

[Bedford]

Hi,

Not sure if you're saying use B12 Before or After the subtraction of 180 as a rule for the division, your OP and post #5 seems to contradict:
2 versions, see if you're getting the results you want:

Book3.xlsx
	B	C	D
13	2179	499.75	399.8
14	1000	410	410
15	3000	470	470
16	2000	455	455
17	1600	473.3333	473.3333
18	2500	464	464
19	1350	585	585
20	1351	585.5	390.3333
Sheet832

Cell Formulas
Range	Formula
C13:C20	C13	=(B13-180)/LOOKUP(B13-180,{0,1351,1651,2001,2501},{2,3,4,5,6})
D13:D20	D13	=(B13-180)/LOOKUP(B13,{0,1351,1651,2001,2501},{2,3,4,5,6})

The result in C13 is exactly correct. The challenge is that with a small number below 1350 for example, B13 is being divided by 3, on my sheet, I'm hoping to use 5 adjacent cells, C15, C16, C17, C18, C19 to result from the formula, however, if B13 is being divided by only 3, the result from the same formula should only appear in C15, C16, if being divided by 4 the result from the same formula should only appear in C15, C16, C17, and if being divided by 5, the result from the same formula should only appear in C15, C16, C17, C18, then finally if divided by 6, the result from the same formula should appear in all adjoining cells C15, C16, C17, C81, C19.

 

[jtakw]

So, can you confirm the results in C12 to C20 are correct for the values in B12 to B20 in my sample?

 

[jasonb75]

So, can you confirm the results in C12 to C20 are correct for the values in B12 to B20 in my sample?

Perhaps it would be better if @Bedford could show some examples with actual values and expected results in order to eliminate any more potential confusion from convoluted descriptions.

 

[Bedford]

So, can you confirm the results in C12 to C20 are correct for the values in B12 to B20 in my sample?

Yes they are indeed correct.