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.

 

[jtakw]

Hi,

No problem, there are different ways to "trap" the error, you can use IFERROR, or test B13 for a Number, this is using IFERROR:

Book3.xlsx
	B	C
13	Enter Value
14
15
Sheet833

Cell Formulas
Range	Formula
C15	C15	=IFERROR(IF(LOOKUP($B13-180,{0,1351,1651,2001,2501},{2,3,4,5,6})>=COLUMNS($B15:C15),($B13-180)/LOOKUP($B13-180,{0,1351,1651,2001,2501},{2,3,4,5,6}),""),"")

 

[Bedford]

Hi,

No problem, there are different ways to "trap" the error, you can use IFERROR, or test B13 for a Number, this is using IFERROR:

Book3.xlsx
	B	C
13	Enter Value
14
15
Sheet833

Cell Formulas
Range	Formula
C15	C15	=IFERROR(IF(LOOKUP($B13-180,{0,1351,1651,2001,2501},{2,3,4,5,6})>=COLUMNS($B15:C15),($B13-180)/LOOKUP($B13-180,{0,1351,1651,2001,2501},{2,3,4,5,6}),""),"")

You did it again, a simple solution, but effective, thank you again!

 

[jtakw]

You're welcome, thanks for the feedback.