I will not code it for you, as 1) you didn't provide your formula anyway, and 2) I would rather teach you how to use Excel...I am a professor after all. =)

To get the Floor (Or Ceiling) of a decimal number, you must use the functions built into Excel!

If A1 contains 4.6, this will put 4 into the cell.

Now, if you are trying to calculate a value and use some logic or limit on your value, you need to dive into expressions.

Consider that I have two values, X and Y. I need to multiply them together, but if X is ever greater than 2, I must use 2, and if the answer is ever over 10, I must say 10, but if the value is ever over 20, I want to leave the cell filled with X's.

Therefore this X*Y=Z has some rules and we want to make one cell calculate our answer.

So, if X > 2, then X = 2

If Z > 20, Z = "XXX"

If Z > 10, Z = 10

Imagine then a table (testing our corner cases and just validating that it works) This will not copy/paste into Excel, because of the website trimming excessive tabs or spaces or something.

1 4

2 4

3 3

5 2

1 10

2 10

3 10

2 12

3 14

Putting =IF(A1>2,2,A1)*B1 into C1 and filling down the row, we get:

1 4 4

2 4 8

3 3 6

5 2 4

1 10 10

2 10 20

3 10 20

3 14 28

Going a step further, to filter out the larger values (>10 and > 20) we need to create a bigger logical test. Every time we use A1, we must get the correct value, then if Z if > 20 we put in X's and if > 10, we round to 10, so we have to embed an if/elseif/else in our formula.

Change cell C1 to:

**Code:**

=IF(IF(A1>2,2,A1)*B1>20,"XXX",IF(IF(A1>2,2,A1)*B1>10,10,IF(A1>2,2,A1)*B1))

Giving us:

1 4 4

2 4 8

3 3 6

5 2 4

1 10 10

2 10 10

3 10 10

2 12 XXX

3 14 XXX

So, the final step is using the FLOOR to round values down.

Our formula, because every time we use A1 we must make sure that it is <= 2 and rounded, the equation explodes in size. Feel free to use Notepad++ or something and paste into Excel if you do this. And don't think this is uncommon, I have had formula that contained 20, 30, 40 if statements in it.

**Code:**

=IF(IF(FLOOR(A1,1)>2,2,FLOOR(A1,1))*B1>20,"XXX",IF(IF(FLOOR(A1,1)>2,2,FLOOR(A1,1))*B1>10,10,IF(FLOOR(A1,1)>2,2,FLOOR(A1,1))*B1))

Expanding our table (shows that floor works the same on 1.1 (1 if rounded) as it would 1.6 (2 if rounded))

1 4 4

2 4 8

3 3 6

5 2 4

1 10 10

2 10 10

3 10 10

2 12 XXX

3 14 XXX

1.1 4 4

2.2 4 8

3.3 3 6

5.5 2 4

1.1 10 10

2.2 10 10

3.3 10 10

2.2 12 XXX

3.3 14 XXX

1.6 4 4

2.6 4 8

3.6 3 6

5.6 2 4

1.6 10 10

2.6 10 10

3.6 10 10

2.6 12 XXX

3.6 14 XXX

Using hidden cells, you can make your formula less complex or at least shorter, but that is another lecture...