# 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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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))))))),"")

HMMMM nothing comes up at all brian... not even a error

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

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

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

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

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?

thanks tac sure does sorry for taking so long to respond

Replies
2
Views
206
Replies
0
Views
583
Replies
6
Views
188
Replies
0
Views
1K
Replies
5
Views
549

1,221,058
Messages
6,157,660
Members
451,431
Latest member
gdekker

### 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