# formula help

#### d0wnt0wn

##### Well-known Member
hi i made this formula base on how much of a certain material a bin can hold..... the problem is that sometimes the formula works out to a .5 1.5 is ok but i dont ever want the total to be just .5 as we will never use less than one bin

=IF(AND(C15<>0,B24="25 SQUARE PER BIN"),ROUNDUP(C15/25,1),IF(AND(C15<>0,B24="20 SQUARE PER BIN"),ROUNDUP(C15/20,1),IF(AND(C15<>0,B24="50 SQUARE PER BIN"),ROUNDUP(C15/50,1),"")))

any way to clean this up and maybe set the minimum number to 1

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Brian from Maui

##### MrExcel MVP
Try,

=IF(AND(C15<>0,B24="25 SQUARE PER BIN"),MAX(1,(ROUNDUP(C15/25,1)),IF(AND(C15<>0,B24="20 SQUARE PER BIN"),MAX(1,ROUNDUP(C15/20,1),IF(AND(C15<>0,B24="50 SQUARE PER BIN"),MAX(1,(ROUNDUP(C15/50,1))))))),"")

#### d0wnt0wn

##### Well-known Member
HMMMM nothing comes up at all brian... not even a error

#### SIXTH SENSE

##### Well-known Member
hi!
try this!
Code:
``=IF(AND(C15<>0,B24="25 SQUARE PER BIN"),ROUNDUP(C15/25,0),IF(AND(C15<>0,B24="20 SQUARE PER BIN"),ROUNDUP(C15/20,0),IF(AND(C15<>0,B24="50 SQUARE PER BIN"),ROUNDUP(C15/50,0),"")))``

I think you should round to 0 and not 1 to have it rounded to the next higher integer value thus 0.5 will be 1

#### d0wnt0wn

##### Well-known Member

ahhh ive tried that already.... see the problem is.... after a minimum value of 1 is returned i want the increments to be able to go up .1 at a time.... i just cant have anything below 1

#### tactps

##### Well-known Member

=IF(ISNUMBER(VALUE(LEFT(B24,2))),MAX(ROUNDUP(C15/VALUE(LEFT(B24,2)),1),1),"")

#### d0wnt0wn

##### Well-known Member

houstan we have a winner!!! thats the one..... i wonder if you could break that formula down for me

#### tactps

##### Well-known Member
First we need to pick up the number to divide by, which is the first 2 characters of the text:
VALUE(LEFT(B24,2))
Note that this will not work if the bin is less than 10 or more than 99 (but the formula can be adjusted if this is required)

Then it looks to see that the first two characters in B24 are a number:
(ISNUMBER(VALUE(LEFT(B24,2)))

If so, then pick up either 1 or the calculated number, whichever is higher:
MAX(ROUNDUP(C15/VALUE(LEFT(B24,2)),1)

If not, then it results in a blank cell ("")

Does that break it down enough for you? Does it make sense?

#### d0wnt0wn

##### Well-known Member
thanks tac sure does sorry for taking so long to respond

Replies
3
Views
174
Replies
4
Views
216
Replies
15
Views
261
Replies
1
Views
475
Replies
0
Views
368

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,840
Messages
5,766,729
Members
425,375
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back