formula help

[d0wnt0wn]

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

 

[Brian from Maui]

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]

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

 

[SIXTH SENSE]

hi!
try this!

=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]

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]

What about:

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

 

[d0wnt0wn]

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

 

[tactps]

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]

thanks tac sure does sorry for taking so long to respond